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
4条答案
按热度按时间tsm1rwdh1#
The
-
character in aPATINDEX
orLIKE
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 usingPatIndex
instead ofCharIndex
and are providing no wildcard characters. Try this:If you want to match a pattern, it has to use wildcards:
Even inside a character class, if first or last, the dash also needs no escaping:
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.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.
I am using in my project and it works fine
6rqinv9w3#
I found the answer from another page here.
How to escape underscore character in PATINDEX pattern argument?
It seems to work, yet I have no idea why.
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:
Basically when it comes to the dash, SQL interprets it as a range delimiter...
Unless you shove it at the END of your pattern.