SQL server error and patindex
I am trying to check phone numbers in NANP format.
I am using this code
patindex('+1[2–9][0-9][0-9][2–9][0-9][0-9][0-9][0-9][0-9][0-9]', n)
But doesn't work as expected, some numbers that should be valid (e.g. +14104536330) don't match the expression.
I created a SQL Fiddle with sample code. What's wrong with my patindex expression?
+3
source to share
1 answer
Copied your line into a hex editor, and it will [2-9]
display as:
+1[2â9][0-9][0-9][2â9][0-9][0-9][0-9][0-9][0-9][0-9]
The hexadecimal code between 2 and 9 is E2 80 93
, which is UTF-8 for "en dash" . So the problem is that you entered a funny version of the dash. This can happen when copying / pasting from Microsoft AutoCorrect environment such as Outlook, Word or Excel. AutoCorrect will silently update the dash to good dashes.
+7
source to share