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

Question

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, contains slash! 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...

Can anybody light my way how to escape slash within the square brackets for LIKE clause here? Many thanks in advance.

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.

Answer 1

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.

Edit based on change to OP

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