PATINDEX、通配符和变量

Question

如何让PATINDEX对包含%字符的变量进行通配符匹配？

在下面，我希望PATINDEX返回‘%3d’的起始位置：

DECLARE @inputText as VARCHAR(100)
DECLARE @s as Int   
DECLARE @cIn as CHAR(3)

SET @inputText = 'OEi49j3DNxE%3d'
SET @cIn = '%3d'
SET @s = PATINDEX('%' + @cIn +'%', @InputText)

正如您从@InputText中看到的，这开始于12位置。

OEi49j3DNxE %3d

然而，PATINDEX似乎在7点返回起始位置，因为它似乎从CIn中删除了%

OEi49j3DNxE%3d

如何查找指定的%3d，而不是3d

Answer 1

您必须用[]包装来转义%符号。为了做到这一点，您必须使变量@cIn更大，以容纳额外的2个字符，只需在执行patindex之前执行替换操作，或者可以在不更改变量大小的情况下内联完成。

DECLARE @inputText as VARCHAR(100)
DECLARE @s as Int   
DECLARE @cIn as CHAR(5)

SET @inputText = 'OEi49j3DNxE%3d'
SET @cIn = '%3d'
SET @cIn = REPLACE(@cIn, '%', '[%]')
SET @s = PATINDEX('%' + @cIn +'%', @InputText)

或

DECLARE @inputText as VARCHAR(100)
DECLARE @s as Int   
DECLARE @cIn as CHAR(5)

SET @inputText = 'OEi49j3DNxE%3d'
SET @cIn = '%3d'
SET @s = PATINDEX('%' + replace(@cIn, '%', '[%]') +'%', @InputText)

您可以在这里阅读更多信息：How do I escape a percentage sign in T-SQL?

Answer 2

您可以使用方括号：

SET @cIn = '[%]3d'

select 
   without_brackets = patindex('%'+'%3d'+'%','OEi49j3DNxE%3d')
 , with_brackets    = patindex('%'+'[%]3d'+'%','OEi49j3DNxE%3d')

rextester演示：http://rextester.com/BVA62284

返回：

+------------------+---------------+
| without_brackets | with_brackets |
+------------------+---------------+
|                7 |            12 |
+------------------+---------------+

Answer 3

DECLARE @inputText as VARCHAR(100)
DECLARE @s as Int   
DECLARE @cIn as CHAR(3)

SET @inputText = 'OEi49j3DNxE%3d'
SET @cIn = '[%]3d'
SET @s = PATINDEX('%' + @cIn + '%' , @InputText)

select @s

产出: 12