Carlos Garces
Carlos Garces

Reputation: 833

SQL server and patindex unspected result

I'm trying to verify phone numbers with NANP format.

I'm 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 not works as expected, some numbers that should be valid (like +14104536330) not match with the expression.

I have created a SQL Fiddle with the sample code. What is wrong in my patindex expression?

Upvotes: 3

Views: 138

Answers (1)

Andomar
Andomar

Reputation: 238086

Copied your string to a hex editor, and the [2-9] show as:

+1[2â9][0-9][0-9][2â9][0-9][0-9][0-9][0-9][0-9][0-9]

The hex code between 2 and 9 is E2 80 93, which is UTF-8 for "en dash". So the problem is basically that you've entered a funny version of a dash. This can happen when you copy/paste out of a Microsoft AutoCorrect environment, like Outlook, Word or Excel. AutoCorrect will silently upgrade your dashes to good looking dashes.

Upvotes: 7

Related Questions