3

I'm trying to use the patindex() function, where I'm matching for the - character.

select PATINDEX('-', table1.col1 )
from table1

Problem is it always returns 0.

The following also didn't work:

PATINDEX('\-', table1.col1 )
from table1
PATINDEX('/-', table1.col1 )
from table1
user38858
  • 306
  • 4
  • 14
  • possible duplicate of [T-SQL : How to escape underscore character in PATINDEX pattern argument?](http://stackoverflow.com/questions/863534/t-sql-how-to-escape-underscore-character-in-patindex-pattern-argument) – piergiaj Aug 05 '14 at 00:44

4 Answers4

6

The - character in a PATINDEX or LIKE pattern string outside of a character class has no special meaning and does not need escaping. The problem isn't that - can't be used to match the character literally, but that you are using PatIndex instead of CharIndex and are providing no wildcard characters. Try this:

SELECT CharIndex('-', table1.col1 )
FROM Table1;

If you want to match a pattern, it has to use wildcards:

SELECT PatIndex('%-%', table1.col1 )
FROM Table1;

Even inside a character class, if first or last, the dash also needs no escaping:

SELECT PatIndex('%[a-]%', table1.col1 )
FROM Table1;

SELECT PatIndex('%[-a]%', table1.col1 )
FROM Table1;

Both of the above will match the characters a or - anywhere in the column. Only if the pattern has characters on either side of the - inside a character class will it be interpreted as a range.

ErikE
  • 48,881
  • 23
  • 151
  • 196
  • Cool, I never knew what Pat stood for. CharIndex() works perfectly. – user38858 Aug 05 '14 at 00:50
  • I agree it's better to use `CharIndex` when no pattern matching is needed. – ErikE Aug 05 '14 at 00:51
  • This was driving me crazy! I was trying to do an update with `REPLACE(Column, '[_]', ' ')` and couldn't figure out why it wasn't working. It should be `REPLACE(Column, '_', ' ')`. – bonh Oct 01 '15 at 14:16
1

Please make sure to use the '-' as the first or last character within wildcard and it will work.

You can even use the below function to replace any special characters. 

CREATE Function [dbo].[ReplaceSpecialCharacters](@Temp VarChar(200))
Returns VarChar(200)
AS
Begin

    Declare @KeepValues as varchar(200)
    Set @KeepValues = '%[-,~,@,#,$,%,&,*,(,),!,?,.,,,+,\,/,?,`,=,;,:,{,},^,_,|]%'
    While PatIndex(@KeepValues, @Temp) > 0

    SET @Temp =REPLACE(REPLACE(REPLACE( REPLACE (REPLACE(REPLACE( @Temp, SUBSTRING( @Temp, PATINDEX( @KeepValues, @Temp ), 1 ),'')   ,' ',''),Char(10),''),char(13),''),'   ',''), '    ','')

Return REPLACE (RTRIM(LTRIM(@Temp)),' ','')
End

I am using in my project and it works fine

LukStorms
  • 28,916
  • 5
  • 31
  • 45
Shekhar
  • 11
  • 1
0

I found the answer from another page here.

How to escape underscore character in PATINDEX pattern argument?

select PATINDEX('%[-]%', table1.col1 )
from table1

It seems to work, yet I have no idea why.

Community
  • 1
  • 1
user38858
  • 306
  • 4
  • 14
  • It's interesting that you provided a link to escaping, but didn't do any escaping... – ErikE Aug 05 '14 at 00:47
  • The top answer (modified) gave me the solution, whatever it's called. – user38858 Aug 05 '14 at 00:49
  • The reason this works is that you added `%` symbols inside the string. This turned it into a pattern, which is what `PatIndex` is looking for. Placing the `-` character inside a character class had no effect in this case. – ErikE Aug 05 '14 at 00:52
0

I know this is an old post but finding a helpful answer in the webiverse has proved fruitless and I want to spare the rest of the world the frustration.
I figured this out myself trying to create a check constraint to validate characters in a phone number - here's my pattern:

CONSTRAINT Chk_Mobile CHECK (PATINDEX('%[^0-9+() -]%',Mobile) = 0)

Basically when it comes to the dash, SQL interprets it as a range delimiter...
Unless you shove it at the END of your pattern.

Corster
  • 1
  • 1