Validate telephone number in SQL Server 2000

Question

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

Define "valid phone number". In what country? What conditions do they have to meet to be valid? What about characters like `(01920) 222222` or `555-1234-6789` — Pekka, Nov 01 '10 at 11:50
Dup of http://stackoverflow.com/questions/32401/validate-a-uk-phone-number, http://stackoverflow.com/questions/1491996/regular-expression-for-uk-telephone-number, http://stackoverflow.com/questions/3669516/regex-uk-landlines-mobile-phone-numbers — Bob Jarvis - Слава Україні, Nov 01 '10 at 12:01
Surely +441332000000 is not valid as you cannot dial that from UL whilst you can the others — mmmmmm, Nov 01 '10 at 12:02
Do you mean the number of digits should be 11? Or are there any other rules that define a UK number. — pavanred, Nov 01 '10 at 12:07
@Mark 01332 is the Derby area code, there is nothing wrong with that format, assuming that the zeros are placeholders. It may be that not all landlines will allow you to dial in international format, but mobiles certainly do. — Colin Pickard, Nov 01 '10 at 12:07
@Bob Jarvis those duplicate questions do not have particularly good answers. You can do much better with phone number validation, if you know the country of the destination number. — Colin Pickard, Nov 01 '10 at 12:11

score 2 · Answer 1 · answered Nov 08 '12 at 15:24

Allow the user to enter numbers in a variety of formats with various dial prefixes and punctuation.

Strip the dial prefix digits, remove spacing and punctuation. Validate the number has the right number of digits and is in a valid range and then store the number in your database in E.164 format with full country code.

This allows you to make bulk area code changes as they happen as well as make it easy to pull all numbers for a country unambiguously.

Store the number without spaces or punctuation.

Use country-specific formatting rules to format the number correctly for display.

UK numbers are quite complex. There's full data here: http://www.aa-asterisk.org.uk/index.php/Regular_Expressions_for_Validating_and_Formatting_GB_Telephone_Numbers

It describes the selection, validation and formatting processes in detail.

score 1 · Answer 2 · answered Nov 01 '10 at 12:05

1

This website has extremely thorough validation for UK telephone numbers, with code examples in JavaScript, VBScript, & PHP. You will need to translate this to use in a SQL Server stored procedure, but the principle should be straightforward to follow.

UK Telephone Number Validation - JavaScript, VBScript, & PHP

answered Nov 01 '10 at 12:05

Colin Pickard

45,724
13
98
148

Unfortunately, T-SQL's string manipulation capabilities are quite lacking, so it's probably not all that easy to "just translate" something like this into T-SQL..... – marc_s Nov 01 '10 at 12:28

score 0 · Accepted Answer · answered Nov 01 '10 at 12:42

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):

check for matches (yes, no)
check for matches (count)
search and replace
format
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.

score -1 · Answer 4 · answered Nov 01 '10 at 12:41

I don't knwo the exact rules that define a valid UK phone number and as you have not provided any rules to validate a Phone no, I just picked rules listed in the url provided by Colin Pickard in his answer.

The following rules are checked for validating UK phone number - 1. Telephone number provided is not null 2. Telephone number does not contain the required 10 or 11 digits. 3. A valid full UK telephone numbers must start with a 0

If there are any rules that I missed out, you can add the check for those conditions too in this function.

ALTER FUNCTION [dbo].[ValidatePhoneNo] 
(   
    @PhoneNo varchar(20) 
)
RETURNS varchar(10)
AS
BEGIN
    DECLARE @Result varchar(10)
    SET @RESULT = 'invalid'

    IF len(@PhoneNo) > 9 AND len(@PhoneNo) < 12 AND @PhoneNo IS NOT NULL AND (substring(@PhoneNo,1,1) = 0)
    BEGIN
        SET @Result = 'valid'
    END

    RETURN @RESULT
END

What happens if there are spaces - so lenghth > 12 and in the question a valis number is +441332000000 which does not being with 0 — mmmmmm, Nov 05 '10 at 19:36
Well, no rules were defined in the question and so I assumed a few and tried to answer it. I have listed my assumptions in my answer. I also mention that I do not know the criteria for a UK number and if I have missed any criteria then an additional check could be easily added to the same function. — pavanred, Nov 05 '10 at 19:53

Validate telephone number in SQL Server 2000

4 Answers4

Linked