3

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?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Carlos Garces
  • 829
  • 1
  • 11
  • 24

1 Answers1

7

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 4
    Just for completeness might be worth mentioning that the pattern then gets treated as a set (I.e matches 2,9, or en dash) rather than a range. – Martin Smith Jun 07 '15 at 20:02