T-SQL - 如何在LIKE子句中转义斜杠/方括号

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

我正在尝试对多个值使用T-SQL LIKE。经过我的研究，最简单的方法似乎是类似于：

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

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

所以我可以期待输出看起来像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:

我的问题是我的场景的元素（A，B，C）格式为“X / Y / Z” - 是的，包含斜杠！斜杠将被视为分隔符 - 与逗号相同。例如，我想选择纽约，东京和伦敦的任何地方，所以我写道：

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...

它将返回US / LA / CBD或Tokyo / Tower ......

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

任何人都可以点亮我的方式如何在LIKE条款的方括号内逃避斜线？提前谢谢了。

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.

我原以为输出是：美国/纽约/日本/东京/英国/伦敦/ E，但实际上所有这些都将被撤出。

4 个解决方案

#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 *. It isn't looking for rows where the entire value is equal to US/New York.

看来你可能对如何在LIKE函数中解释[]模式有误解。当你有'[US / New York]％'这样的模式时，它会说“查找以下列任何字符开头的值U，S，/，N，e，w，（空格），Y，o， r，或k。因此，这种模式会找到南非或外蒙古的价值。它不会寻找整个价值等于美国/纽约的行。

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

实现目标的一种方法是使用多个Or语句：

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

#2

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,..

它将检索X / Y / Z1，X / Y / Z，..

#3

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

#4

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

这将匹配像blabla / A / A1和xxx / B / B1这样的字符串

Edit: 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:

以斜线B结尾：

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

Ending in slash 'any single character'

以斜线“任何单个字符”结尾

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

Starting with Londen -- any number of chars -- ending in slash 'any single character'

从Londen开始 - 任意数量的字符 - 以斜线“任何单个字符”结尾

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

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

- 任何地方都有Londen，以斜线“任何单个角色”结尾

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