Using the Like Operatior in a where clause with a variable which could be a number or a varchar

Question

In a stored procedure I am using a where clause similar to the following :

Declare @pBar_Code varchar(51)
Set @pBar_Code=convert(varchar(51),UPPER(@Bar_Code))


Select  barcode from XYZ

where UPPER(bar_code)  like Concat('%',@pBar_Code,'%')


The @pBar_Code variable can be either text or numeric. The barcode column in table XYZ is varchar(51)


I receive the following error message, when setting @pBar_Code=512 or another number. The variable should allow numbers, text or both.


Msg 8114, Level 16, State 5, Line 8
Error converting data type varchar to numeric.


What do I need to do in order to utilize the Like operator to do a partial lookup for any rows that 

Msg 8114, Level 16, State 5, Line 8
Error converting data type varchar to numeric.

Declare @x Table (Id int Identity(1,1), barcode varchar(51))
Insert into @x(barcode)
Values ('abc'),
('123'),
('544')

Answer 1

Hi @vsslasd ,

Please provide your whole query since I worked with your query provided but could not reproduce your error as below:

Declare @x Table (Id int Identity(1,1), barcode varchar(51))  
Insert into @x(barcode)  
Values ('abc'),  
('123'),  
('544')  
  
Declare @pBar_Code varchar(51)  
Set @pBar_Code=512  
      
Select  barcode from @x  
where UPPER(barcode) like Concat('%',@pBar_Code,'%')  

Below is one example which would get 'Error converting data type varchar to numeric.'.

select cast(isnull('abcd','') as decimal(15,4))  

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

In this case, we could not convert the 'abcd' to one number indeed.

But we could use try_convert which returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

select try_cast(isnull('abcd','') as decimal(15,4))  --NULL  

Or replace the 'abcd' with a correct number.

select cast(isnull('512','') as decimal(15,4))  

In addition, you used CONCAT which would take care of converting to the appropriate data type for you in your query so you would not get Error converting data type varchar to numeric error with this part.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.