user38858 user38858 - 1 year ago 72
SQL Question

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

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


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 Source

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're using PatIndex instead of CharIndex, but 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download