question

vsslasd-3371 avatar image
0 Votes"
vsslasd-3371 asked vsslasd-3371 commented

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

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')

sql-server-transact-sqlsql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered vsslasd-3371 commented

Hi @vsslasd-3371,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you!!

0 Votes 0 ·