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