T-SQL - Avoiding slash / in square brackets in LIKE clause

Question

I am trying to use T-SQL LIKE for multiple values. After my research, the easiest way looks something like this:

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

So, I can expect the result to look like A1, B2, C3 ...

My problem is that the elements (A, B, C) for my script are in the format "X / Y / Z" - yes, it contains a slash! And the slash will be considered as a separator - the same as a comma. For example, 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 he does the same thing as

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

And he will return the USA / Los Angeles / CBD or Tokyo / Tower ...

Can someone ignite my path how to avoid the slash in square brackets for a LIKE clause? Thank you very much in advance.

Here is an example 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 script project:

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

I expected the output to be: USA / New York / A Japan / Tokyo / C United Kingdom / London / E but in fact they will all be withdrawn.

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' 

In this case there is no need to avoid. You can simply use an expression with a trailing pattern.

Change based on change to OP

It looks like you may have a misconception about how the template [] interpreted in the LIKE function. When you have a pattern like '[US/New York]%' , it says "Find values ​​starting with any of the following characters U , S , / , N , e , w , (space), Y , o , r or k . Thus, such a pattern will find the value South Africa or Outer Mongolia . It does not look for strings where all the value is US/New York .

One way to achieve what you are looking for is to use several Or statements:

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

Answer 2

Try this option -

 DECLARE @temp TABLE (name NVARCHAR(50)) INSERT INTO @temp (name) VALUES ('Ben S'), ('test') SELECT DISTINCT t.* FROM @temp t CROSS JOIN ( SELECT * FROM (VALUES ('A'), ('B'), ('C')) t(t2) ) t2 WHERE t.name LIKE '%' + t2 + '%' 

Or try this -

 SELECT t.* FROM @temp t WHERE EXISTS( SELECT 1 FROM (VALUES ('A'), ('B'), ('C')) c(t2) WHERE t.name LIKE '%' + t2 + '%' ) 

Answer 3

 declare @str nvarchar(10); set @str='X/Y/Z'; SELECT name FROM dbo.Slash WHERE name LIKE @str+'%' 

It will extract X / Y / Z1, X / Y / Z, ..

Answer 4

Try separating path separators from valid character ranges.

 WHERE name LIKE '%[/][AZ][/][AZ]_' 

This will match strings like blabla / A / A1 and xxx / B / B1

Edit: brackets are treated as a "valid character set". Based on your paraphrased question, I think you could get just by combining some wildcards with some literal values. Here are some examples:

Slash ending B:

 SELECT * FROM @temp WHERE Location LIKE '%/B' 

Completion in a slash line of any character

 SELECT * FROM @temp WHERE Location LIKE '%/_' 

Starting with Londen - any number of characters - ends in a slash of any character

 SELECT * FROM @temp WHERE Location LIKE 'London%/_' 

- have Londen anywhere, ending in a slash line “any single character”

 SELECT * FROM @temp WHERE Location LIKE '%London%/_'