sql-servert-sqlescapingsql-likeslash

T-SQL - How to escape a slash / in square brackets in LIKE clause


I'm trying to use T-SQL LIKE against multiple values. After my research, the easiest way seems to be something similar to:

SELECT Column1
FROM Table_1
WHERE Column1 LIKE '[A,B,C]%'

So that I can expect the output looks like A1,B2,C3...

My problem is that the elements(A,B,C) for my scenario are in the format of "X/Y/Z"—yes, it contains slashes! And slash will be treated as a delimiter—the same as comma. For instance, I want to select any places in New York, Tokyo and London, so I wrote:

WHERE Location LIKE '[US/New York, Japan/Tokyo, UK/London]%'

But it does the same as

WHERE Location LIKE '[US,New York, Japan, Tokyo, UK, London]%'

And it will return US/LA/CBD or Tokyo/Tower...

How can I escape slash within the square brackets for the LIKE clause here?

Here is the sample table:

DECLARE @temp TABLE (Location NVARCHAR(50))
INSERT INTO @temp (Location ) VALUES ('US/New York/A')
INSERT INTO @temp (Location ) VALUES('New York/B')
INSERT INTO @temp (Location ) VALUES ('Japan/Tokyo/C')
INSERT INTO @temp (Location ) VALUES ('Tokyo/D')
INSERT INTO @temp (Location ) VALUES ('UK/London/E')
INSERT INTO @temp (Location ) VALUES('London/F')

And below is my draft script:

SELECT *
FROM @temp
WHERE Location LIKE '[US/New York, Japan/Tokyo, UK/London]%'

I was expecting the output is:

US/New York/A
Japan/Tokyo/C
UK/London/E

But actually all of them will be pulled out.


Solution

  • DECLARE @temp TABLE ( Location NVARCHAR(50)    )
    
    INSERT @temp (Location )
    VALUES ('US/New York/A')
        , ('New York/B')
        , ('Japan/Tokyo/A')
        , ('Tokyo/B')
        , ('UK/London/A')
        , ('London/B')
    
    Select *
    From @temp
    Where Location Like '%/A'
    

    There is no need to escape the / in this case. You can simply use an expression with a trailing wildcard.

    For the new version of the question

    It appears you may have a misconception about how the [] pattern is interpreted in the LIKE function. When you have a pattern like '[US/New York]%', it is saying "Find values that start with any of the following characters U,S,/,N,e,w, (space), Y, o,r, or k. Thus, such a pattern would find a value South Africa or Outer Mongolia. It isn't looking for rows where the entire value is equal to US/New York.

    One way to achieve what you seek is it to use multiple Or statements:

    Select *
    From @temp
    Where Location Like 'US/New York%'
        Or Location Like 'Japan/Tokyo%'
        Or Location Like 'UK/London%'