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

ux6nzvsh  于 2023-03-17  发布在  其他
关注(0)|答案(4)|浏览(122)

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
tsm1rwdh

tsm1rwdh1#

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.

nkhmeac6

nkhmeac62#

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

6rqinv9w

6rqinv9w3#

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.

dwthyt8l

dwthyt8l4#

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.

相关问题