none
extracting valid mobile numbers

    Question

  • hi All,

    im trying to

    extract the valid mobile numbers with out any other charecters(*,/,+ etc..) only numeric 10 digited number.

    EX:*091234567890--output should be 91234567890 

    Ex2:091234567890**--91234567890 

    so dynamically i will get 10 digits in sequence i get but i should consider only 10 digits to output

    declare @mobile table(MOBILE varchar(16),Result VARCHAR(10))
    insert into @mobile
    select '(+91)-9234567890','VALID' union
    select '92345678990*','VALID' union all
    select '91-9234567890','VALID' union all
    select '(91)9234567890','VALID' union all
    select '+9234567890*','VALID' union all
    select '(9234567890','VALID' union all
    select '09234567890' ,'VALID' union all
    select '9234567890-','VALID' union all
    select '+923456789*' ,'INVALID' union all
    select '+234567890*' ,'INVALID' union all
    select '97(31)-789889','INVALID' union all
    select '+234567890&' ,'INVALID'
    --
    select * from @mobile




    Tuesday, November 19, 2013 6:06 PM

Answers

  • You can use Regular Expression CLR Function to clear all none numeric data in one time without lopping. Moreover if you find an expression which fit the data you are looking for the you can do it all in one simple function. This will need some thinking maybe & understanding you requirements... i am not sure i do now(step one: clear none numeric is obvious, but the last part i am not sure i got).

    Do you want a solution with CLR? If so i can guide you on the C# code (1 line of code) and the installation. Then we will need to find the Regular Expression. It will a better solution then using pure TSQL.


    [Personal Site] [Blog] [Facebook]signature

    Tuesday, November 19, 2013 9:31 PM

All replies

  • Create a function to strip off all the non numeric characters like below:

    CREATE FUNCTION [dbo].[GetNumericPhone]
    (@strAlphaNumeric VARCHAR(20))
    RETURNS VARCHAR(15)
    AS
    BEGIN
    DECLARE @intAlpha BIGINT
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
    BEGIN
    WHILE @intAlpha > 0
    BEGIN
    SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END
    END
    RETURN ISNULL(@strAlphaNumeric,0)
    END
    

    Usage:

    select [dbo].[GetNumericPhone](MOBILE) from @mobile

    Tuesday, November 19, 2013 6:29 PM
  • this function will help me to some extent to extract the Numeric Numbers.

    but Ex:9191234567890 or 091234567890  in this my mobile number part is last 10 digits.


    it may be changing to left some time Right.

    here one thing we can make sure is it never start with Number <6 mean(starting number will be between [6,9])

    my dialer will recognise on 10 digited number.

    Tuesday, November 19, 2013 7:07 PM
  • Adjust  but tweaking sometime like below:

     select *,RIGHT([dbo].[GetNumericPhone](RTRIM(MOBILE)),10) from @mobile
     Where LEN(RIGHT([dbo].[GetNumericPhone](RTRIM(MOBILE)),10))>=10


    • Edited by Taherul673 Tuesday, November 19, 2013 7:34 PM
    Tuesday, November 19, 2013 7:33 PM
  • You can use Regular Expression CLR Function to clear all none numeric data in one time without lopping. Moreover if you find an expression which fit the data you are looking for the you can do it all in one simple function. This will need some thinking maybe & understanding you requirements... i am not sure i do now(step one: clear none numeric is obvious, but the last part i am not sure i got).

    Do you want a solution with CLR? If so i can guide you on the C# code (1 line of code) and the installation. Then we will need to find the Regular Expression. It will a better solution then using pure TSQL.


    [Personal Site] [Blog] [Facebook]signature

    Tuesday, November 19, 2013 9:31 PM