0

How do you get PATINDEX to to do a wildcard card match on a variable that includes a % character?

In the following I want PATINDEX to return the starting position of '%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)

As you can see from the @InputText, this starts at position 12.

OEi49j3DNxE %3d

However PATINDEX seems to return the start position at 7 because it seems to drop the % from CIn:

OEi49j3DNxE%3d

How do I get look for %3d as specified, rather than 3d?

Matt Hall
  • 2,412
  • 7
  • 38
  • 62

3 Answers3

2

You can use square brackets: 

SET @cIn = '[%]3d'

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

rextester demo: http://rextester.com/BVA62284

returns: 

+------------------+---------------+
| without_brackets | with_brackets |
+------------------+---------------+
|                7 |            12 |
+------------------+---------------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • Totally correct - only went with @Avitus as the alternative provided worked better in my full code. Thanks. – Matt Hall May 06 '17 at 15:44
1

You have to escape the % sign by wrapping with []. In order to do this you will have to make your variable @cIn larger to accommodate the extra 2 characters and just do a replace before you do the patindex or you can do it inline without changing variable sizes.

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)

OR

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)

You can read more about here: How do I escape a percentage sign in T-SQL?

Community
  • 1
  • 1
Avitus
  • 15,640
  • 6
  • 43
  • 53
  • Voted as answer as the alternative option provided worked for me (`cIn` is being used elsewhere, and storing it as `[%]3d` causes other problems). Thanks for this :) – Matt Hall May 06 '17 at 15:43
1
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

Output: 12

GandRalph
  • 590
  • 2
  • 10
  • Correct, thank you very much - only went with @Avitus as the alternative provided worked better in my full code. Thanks. – Matt Hall May 06 '17 at 15:45