Using variable in SQL LIKE statement

Question

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

User · Answer

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

User · Answer

It may be as simple as LIKE      3     being   3  the input variable   This works for me with SAP B1 9 1

User · Answer

As Andrew Brower says  but adding a trim  ALTER PROCEDURE  lt Name gt         PartialName VARCHAR 50    NULL    SELECT Name      FROM  lt table gt      WHERE Name LIKE       LTRIM RTRIM  PartialName

User · Answer

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

User · Answer

DECLARE  SearchLetter2 char 1   Set this to a longer char

User · Answer

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

User · Answer

We can write directly too     DECLARE  SearchLetter CHAR 1    SET  SearchLetter    A    SELECT    FROM   CUSTOMERS  WHERE  CONTACTNAME LIKE  SearchLetter               AND REGION    WY     or the following way as well if we have to append all the search characters then   DECLARE  SearchLetter CHAR 1    SET  SearchLetter    A          SELECT    FROM   CUSTOMERS  WHERE  CONTACTNAME LIKE  SearchLetter         AND REGION    WY     Both these will work

User · Answer

DECLARE  SearchLetter2 char 1   Set this to a longer char

User · Answer

DECLARE  SearchLetter2 char 1   Set this to a longer char

User · Answer

It may be as simple as LIKE      3     being   3  the input variable   This works for me with SAP B1 9 1

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

But in my opinion one important thing   The  char number   it s lenght of variable   If we ve got table with  Names  like for example  Test1  Test200  and we declare char 5  in SELECT like   DECLARE  variable char 5  SET  variable    Test1   SELECT   FROM table WHERE Name like  variable   the result will be only -  Test1    char 5  - 5 chars in lenght  Test11 is 6    The rest of potential interested data like  Test11  Test200  will not be returned in the result   It s ok if we want to limit the SELECT by this way   But if it s not intentional way of doing it could return incorrect results from planned   Like  all Names begining with Test1         In my opinion if we don t know the precise lenght of a SELECTed value  a better solution could be something like this one   DECLARE  variable varchar max  SET  variable    Test1   SELECT   FROM  lt table gt  WHERE variable1 like  variable   This returns  Test1 but also Test11  Test19 and Test100  Test199

User · Answer

As Andrew Brower says  but adding a trim  ALTER PROCEDURE  lt Name gt         PartialName VARCHAR 50    NULL    SELECT Name      FROM  lt table gt      WHERE Name LIKE       LTRIM RTRIM  PartialName

User · Answer

If you are using a Stored Procedure   ALTER PROCEDURE  lt Name gt         PartialName VARCHAR 50    NULL    SELECT Name      FROM  lt table gt      WHERE Name LIKE        PartialName

User · Answer

We can write directly too     DECLARE  SearchLetter CHAR 1    SET  SearchLetter    A    SELECT    FROM   CUSTOMERS  WHERE  CONTACTNAME LIKE  SearchLetter               AND REGION    WY     or the following way as well if we have to append all the search characters then   DECLARE  SearchLetter CHAR 1    SET  SearchLetter    A          SELECT    FROM   CUSTOMERS  WHERE  CONTACTNAME LIKE  SearchLetter         AND REGION    WY     Both these will work

User · Answer

If you are using a Stored Procedure   ALTER PROCEDURE  lt Name gt         PartialName VARCHAR 50    NULL    SELECT Name      FROM  lt table gt      WHERE Name LIKE        PartialName

User · Answer

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

User · Answer

But in my opinion one important thing   The  char number   it s lenght of variable   If we ve got table with  Names  like for example  Test1  Test200  and we declare char 5  in SELECT like   DECLARE  variable char 5  SET  variable    Test1   SELECT   FROM table WHERE Name like  variable   the result will be only -  Test1    char 5  - 5 chars in lenght  Test11 is 6    The rest of potential interested data like  Test11  Test200  will not be returned in the result   It s ok if we want to limit the SELECT by this way   But if it s not intentional way of doing it could return incorrect results from planned   Like  all Names begining with Test1         In my opinion if we don t know the precise lenght of a SELECTed value  a better solution could be something like this one   DECLARE  variable varchar max  SET  variable    Test1   SELECT   FROM  lt table gt  WHERE variable1 like  variable   This returns  Test1 but also Test11  Test19 and Test100  Test199

User · Answer

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 · Answer

DECLARE  SearchLetter2 char 1   Set this to a longer char

[sql-server] Using variable in SQL LIKE statement

Examples related to sql-server