Search code examples
sqlsql-serversql-server-2000

Validate telephone number in SQL Server 2000

Does anyone have a nifty way of validating telephone numbers using sql (SQL Server 2000).

I need to select all users fro ma Db that have valid phone number

Thanks Sp

Valid number 
08450000000
01332000000
07444000000
+441332000000

Standard UK numbers

Solution

  • If you have a regular expression to match the number, you can install a regex extended stored procedure on your SQL Server. I installed this extended stored procedure at work and we use it quite a bit. There are several procedures (each with corresponding function wrappers):

    1. check for matches (yes, no)
    2. check for matches (count)
    3. search and replace
    4. format
    5. split

    To find matches you would use it as such:

     select number 
 from numberTable
 where dbo.fn_pcre_match(number, 'someRegex') = 1

    Where 'someRegex' is the regular expression matching the format you are looking for. This site has some matches on it, but I'm not sure how well they work since I'm not familiar with UK numbers whatsoever.