Post reply

How to filter invalid Phone number & Email address in sql

  • December 9, 2014 at 5:37 am

    #397753

    Hi All,

    I have customer_Contact table with customer and contact details as below.

    CUSTOMER CONTACT

    123 abc@gmail.com

    234 1234567890

    345 040 12345678

    We have so many Invalid records which are having special chanracters(!@#$%^&*()+=:"<>?;',./[]{}\|) in the phone numbers & email address as well. There are other types of invalids like (NONE,XXXXX,only dot,DONT WANT TO MENTION,get pls,check some where09).

    How can we filter all this invalids and only show valid records .

    I Have added below script in my where clause to filetr those invalids, but is there any simpler or best way to do this.

    SELECT * FROM @TT WHERECHARINDEX(' ',LTRIM(RTRIM(contact))) = 0AND LEFT(LTRIM(contact),1) <> '@'AND RIGHT(RTRIM(contact),1) <> '.'AND CHARINDEX('.',contact , CHARINDEX('@',contact))- CHARINDEX('@',EA ) > 1AND LEN(LTRIM(RTRIM(EA )))- LEN(REPLACE(LTRIM(RTRIM(EA)),'@','')) = 1AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(contact)))) >= 3AND (CHARINDEX('.@',contact ) = 0AND CHARINDEX('..',contact ) = 0) or (Len(Ltrim(Rtrim(COntact)))=10 and contact like '%[0-9]%' )or (Len(Ltrim(Rtrim(COntact)))=11 and contact like '%[0-9]%')

    and contact not like '%[(!@#$%^&*()+=:"<>?;',./[]{}\|]%'

  • December 9, 2014 at 6:04 am

    #1763492

    search for IsValidEmail here on SQL server Central; there's a few threads that looked into it pretty deeply; there's tsql versions similar to what you are testing, as well as CLR examples on here.

    if you are willing to add a CLR function, this thread has some good examples and explanations for some great performaing functions if you have tons of data:

    http://qa.sqlservercentral.com/Forums/Topic1406771-386-1.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply