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降低了%：

OEI49J 3D nxe％3D

我如何根据指定而不是3d来查找%3d？

Answer 1

您可以使用方括号：

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 2

您必须通过与[]包装来逃避％符号。为了做到这一点，您将必须使您的变量@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)

您可以在此处阅读更多有关：如何逃脱T-SQL中的百分比？

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