CODEWITHSUNDEEP
sql-servert-sqlescapingsql-likeslash
Ben S
Ben S

Reputation: 159

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.

Upvotes: 7

Views: 44346

Answers (4)

Thomas
Thomas

Reputation: 64674

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

Upvotes: 1

souplex
souplex

Reputation: 981

Try separating the path separators from the valid character ranges.

WHERE name LIKE '%[/][A-Z][/][A-Z]_'

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

The brackets are treated like 'a collection of allowed characters'. Based on your rephrased question, I think you could get by by simply combining some of the wildcard characters with some literal values. Here are some samples:

Ending with slash B:

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

Ending in slash 'any single character'

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

Starting with London—any number of chars—ending in slash 'any single character'

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

-- having London anywhere, ending with in slash 'any single character'

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

Upvotes: 0

Devart
Devart

Reputation: 122042

Try this one -

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

Upvotes: 0

neetz
neetz

Reputation: 140

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

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

Upvotes: 0

Related Questions