SQL need to match the '-' char in patindex() function

Question

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

Answer 1

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.

Answer 2

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