Using variable in SQL LIKE statement

JoelCogley_42568 0
Viewed 272902

I've got a sproc (MSSQL 2k5) that will take a variable for a LIKE claus like so:

DECLARE @SearchLetter2 char(1)
SET @SearchLetter = 't'
SET @SearchLetter2 = @SearchLetter + '%'
SELECT *
    FROM BrandNames 
    WHERE [Name] LIKE @SearchLetter2 and IsVisible = 1 
    --WHERE [Name] LIKE 't%' and IsVisible = 1 
    ORDER BY [Name]

Unfortunately, the line currently running throws a syntax error, while the commented where clause runs just fine. Can anyone help me get the un-commented line working?

sql-server
10 Answers

Joel is it that @SearchLetter hasn't been declared yet? Also the length of @SearchLetter2 isn't long enough for 't%'. Try a varchar of a longer length.

esabine_14931570

This works for me on the Northwind sample DB, note that SearchLetter has 2 characters to it and SearchLetter also has to be declared for this to run:

declare @SearchLetter2 char(2)
declare @SearchLetter char(1)
Set @SearchLetter = 'A'
Set @SearchLetter2 = @SearchLetter+'%'
select * from Customers where ContactName like @SearchLetter2 and Region='WY'
JB_King_87450

DECLARE @SearchLetter2 char(1)

Set this to a longer char.

Jonathan_Holland_19650

I had also problem using local variables in LIKE.
Important is to know: how long is variable.
Below, ORDER_NO is 50 characters long, so You can not use: LIKE @ORDER_NO, because in the end will be spaces.
You need to trim right side of the variable first.
Like this:

DECLARE @ORDER_NO char(50)
SELECT @ORDER_NO = 'OR/201910/0012%'

SELECT * FROM orders WHERE ord_no LIKE RTRIM(@ORDER_NO)
user_121823970

It may be as simple as LIKE '%%[%3]%%' being [%3] the input variable.

This works for me with SAP B1 9.1

user_104215350

I ran into a similar problem. I needed to use just a small piece of a URL saved in my database where the front and ends were irrelevant.

I first attempted to use:

DECLARE @variable VARCHAR(250) = %x%;
SELECT * FROM tblone WHERE column1 LIKE '@variable'

However, this returned the error:

Arithmetic overflow error converting numeric to data type varchar

My working query was formatted:

DECLARE @variable VARCHAR(1000) = x;
SELECT * FROM tblone WHERE column1 LIKE '%'+@variable+'%'
user_197966790
Related