Syntax - How to use variable in LIKE clause

TreyS 166 Reputation points
2024-06-20T16:18:26.5933333+00:00

Trying to use a variable in the LIKE clause of a SELECT statement.

Hope this is simple, but available guidance hasn't worked.

As seen below, query works when a "fixed" value is used with wildcard characters, but I haven't managed to get it with the variable...

--The Workdays column, in c_Persons table, is NCHAR(7)

--This works
--SELECT * FROM c_Persons WHERE Workdays LIKE '%3%'

DECLARE @DOW NCHAR(7)

SET @DOW = '%3%'
--This returns nothing.
--SELECT * FROM c_Persons WHERE Workdays LIKE @DOW

SET @DOW = '3'
--This returns nothing.
--SELECT * FROM c_Persons WHERE Workdays LIKE '%@DOW%'

--This returns nothing.
SELECT * FROM c_Persons WHERE Workdays LIKE '%' + @DOW + '%'


Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,592 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 105.8K Reputation points MVP
    2024-06-20T21:57:17.5333333+00:00

    You have declare @DOW as nchar(7). That is, it's fixed length of seven characters. So if you do:

    SET @DOW = '%3%'
    --This returns nothing.
    --SELECT * FROM c_Persons WHERE Workdays LIKE @DOW
    

    You are search for strings that include the digit three followed by six spaces.

    Change the datatype to nvarchar(7), and you should be fine.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 42,761 Reputation points
    2024-06-20T16:30:35.9033333+00:00

    --This returns nothing. --SELECT * FROM c_Persons WHERE Workdays LIKE '%@DOW%'

    Of course not, I don't know a day name containing "@DOW".

    Please post table design as DDL, some sample data as DML and the expected result.