none
phonenumber, zipcode validation in sql server

    Question

  • hi all i have a table with two fields us Phone format and US postal code, i am having trouble validating these both fields

    555-555-1234
    (555)-555-2345
    5555551234
    1234567890-12
    123-234-5678-1

    the above are valid phone formats including extensions

    90201
    63038-2345

    the aobve are the valid postal code

    can anybody write the sql statements to validate these.

    help please. 

    Monday, June 06, 2011 4:24 AM

All replies

  • You can try CLR. http://www.dreamincode.net/code/snippet3209.htm
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, June 06, 2011 5:19 AM
    Moderator
  • First of all try to remove the noise characters from your data.

    Even if the hyphen & brackets are valid for a telephone number it will still create problem validating them due to inconsistency across data.

    Your phone number could also be like this: 5555551234 x987

    So your code should contain a chain of REPLACE() to remove noise chars.

    Try this:

    declare @T Table (phone varchar(20))
    
    insert into @T
    select '555-555-1234' union
    select '(555)-555-2345' union
    select '5555551234x987' union
    select '1234567890-12' union
    select '123-234-5678-1'
    
    select phone as Orig_Phone,
      replace(replace(replace(replace(phone,'-',''),'x',''),char(40),''),char(41),'') as CleanPhone,
      isnumeric(replace(replace(replace(replace(phone,'-',''),'x',''),char(40),''),char(41),'')) isValid
    from @T
    
    


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Monday, June 06, 2011 5:22 AM
  • You can try CLP or use Regular Expression.
    Monday, June 06, 2011 5:38 AM
  • You can use Check constraints in SQL Server while inserting the data in tables. here are some example for formatting the phone numbers... may be it will help you.

    If you want data validation in Frontend of your application , you can use Regular Expression to validate it....

     

    http://www.sqlservercentral.com/articles/Administration/usingcheckconstraints/815/

     


    If this answer is helpful to you .. Please mark as Answer....
    Monday, June 06, 2011 7:59 AM
  • Hi

    These might be helpful

    http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

    http://justgeeks.blogspot.com/2008/08/adding-regular-expressions-regex-to-sql.html


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
    Monday, June 06, 2011 8:28 AM
  • You can extend the following script perhaps,

    ALTER FUNCTION udf_ValidatePhone (
    
    	@phone VARCHAR(20),
    
    	@format VARCHAR(20)
    
    )
    
    RETURNS TINYINT
    
    BEGIN
    
    	DECLARE @r TINYINT, @i TINYINT = 0
    
    	DECLARE @temp_phone VARCHAR(20) = @phone
    
    	SET @r = 0
    
    	WHILE @i <= 9
    
    	BEGIN
    
    		SET @temp_phone = REPLACE(@temp_phone,@i,'0')
    
    		SET @i = @i + 1
    
    	END
    
    	IF @temp_phone = @format
    
    		SET @r = 1
    
    	
    
    	RETURN @r
    
    END
    
    GO
    
    
    
    SELECT dbo.udf_ValidatePhone('(216)528-9423','(000)000-0000')
    
    
    
    

     


    SQL Server Tutorials

    Monday, June 06, 2011 9:10 AM
    Moderator