locked
Format cell Number as 0xxxxxxxxxx RRS feed

  • Question

  • Function getnumber(ByVal str As String) As String
            'remove all characters except integer
            str = Regex.Replace(str, "[^0-9]", "")
            If str.Length >= 11 Then
                'cut the last 11 integer
                str = str.Substring(str.Length - 11)
                Return str
            ElseIf str.Length = 10 Then
                'add just one 0
                str = "0" + str
                Return str
            End If
            Return "No result"
        End Function
    I need a ms sql function of equailavent of the aboce vb.net function. any help appreciated...

    VHO

    Wednesday, November 7, 2012 9:46 PM

Answers

  • plz try this

    declare @thestring varchar(50) 
    set @thestring = '(0543) 978 1632' 
    declare @final varchar(50) 
    set @final = '' 
    
    select @final = @final + x.thenum 
    from 
    ( 
        select substring(@thestring, number, 1) as thenum, number 
        from master..spt_values 
        where substring(@thestring, number, 1) like '[0-9]' and type='P'
    ) x 
    order by x.number 
    set @final = LTRIM(RTRIM (@final))
    --print len (@final )
    if len (@final )>= 11
        set @final = Substring(@final,len (@final)-10, 11)
    else if len (@final )= 10
        set @final = '0'+ @final
    else
        set @final = @thestring print @final

    cases

    +95556214972 give 95556214972 since original string contains 11 digits

    +955562149721 give  55562149721 by removing +9 we are getting 11 digits

    for this you may have add cases accordingly

    Thanks

    Johnson


    • Edited by Johnson T A Friday, November 9, 2012 1:16 PM
    • Marked as answer by emmim44 Tuesday, November 13, 2012 9:21 AM
    Friday, November 9, 2012 12:57 PM

All replies

  • If you are already doing the display formatting in the proper place (front end application), why are you trying to do it in the improper place (the database)?

    Here are a couple of Kluge examples:

    Declare @tvTable Table (
    	NotANumber varchar(50)
    )
    
    Insert	@tvTable
    Select	'12345678901'
    Union All
    Select	'1234567890'
    Union All
    Select	'11111111111'
    Union All
    Select	'12354'
    Union All
    Select	'234890712349573405893'
    Union All
    Select	'1-234-567-8901'
    Union All
    Select	'123-456-7890'
    
    Select	*
    		,Case Len(NotANumber)
    			When 11 Then NotANumber
    			When 10 Then '0' + NotANumber
    			Else 'No Result'
    		End As [MightBeANumber]
    		,Case Len(Replace(NotANumber, '-', ''))
    			When 11 Then Replace(NotANumber, '-', '')
    			When 10 Then '0' + Replace(NotANumber, '-', '')
    			Else 'No Result'
    		End As [ProbablyStillNotANumber]
    From	@tvTable

    Wednesday, November 7, 2012 10:46 PM
  • Check this function as your starting point

    Extracting numbers with SQL Server


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by dgjohnson Wednesday, November 7, 2012 10:58 PM
    Wednesday, November 7, 2012 10:51 PM
  • If you are already doing the display formatting in the proper place (front end application), why are you trying to do it in the improper place (the database)?

    Here are a couple of Kluge examples:

    Declare @tvTable Table (
    	NotANumber varchar(50)
    )
    
    Insert	@tvTable
    Select	'12345678901'
    Union All
    Select	'1234567890'
    Union All
    Select	'11111111111'
    Union All
    Select	'12354'
    Union All
    Select	'234890712349573405893'
    Union All
    Select	'1-234-567-8901'
    Union All
    Select	'123-456-7890'
    
    Select	*
    		,Case Len(NotANumber)
    			When 11 Then NotANumber
    			When 10 Then '0' + NotANumber
    			Else 'No Result'
    		End As [MightBeANumber]
    		,Case Len(Replace(NotANumber, '-', ''))
    			When 11 Then Replace(NotANumber, '-', '')
    			When 10 Then '0' + Replace(NotANumber, '-', '')
    			Else 'No Result'
    		End As [ProbablyStillNotANumber]
    From	@tvTable


    " +905439781632 or 905439781632 or (0543) 978 1632  " should be converted as "05439781632".... There should not be any spaces/other characters between digits...And Above statement says no result. And I need to use this to update all cell phone numbers in db with this format "0XXXXXXXXXX" 

    VHO

    Thursday, November 8, 2012 7:44 AM
  • Well, using my Kluge and given your new data, this works:

    Select	*
    		,Case Len(NotANumber)
    			When 11 Then NotANumber
    			When 10 Then '0' + NotANumber
    			Else 'No Result'
    		End As [MightBeANumber]
    		,Case Len(Replace(Replace(Replace(Replace(Replace(NotANumber, '(', ''), ')', ''), '+', ''), ' ', ''), '-', ''))
    			When 11 Then Replace(Replace(Replace(Replace(Replace(NotANumber, '(', ''), ')', ''), '+', ''), ' ', ''), '-', '')
    			When 10 Then '0' + Replace(Replace(Replace(Replace(Replace(NotANumber, '(', ''), ')', ''), '+', ''), ' ', ''), '-', '')
    			Else 'No Result'
    		End As [ProbablyStillNotANumber]
    From	@tvTable

    However, you will probably be better served by the query presented in Naomi's link.  For your scenario:

    ;with cteCellNumbers as
    (
    	Select	NotANumber
    			,Left(	SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000),
    					PatIndex('%[^0-9.-]%', SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000) + 'X')-1) As Number
    	From	@tvTable
    )
    Select	Case Len(Number)
    			When 11 Then Number
    			When 10 Then Number
    			Else 'No Result'
    		End
    From	cteCellnUMBERS

    Thursday, November 8, 2012 2:06 PM
  • Well, using my Kluge and given your new data, this works:

    Select	*
    		,Case Len(NotANumber)
    			When 11 Then NotANumber
    			When 10 Then '0' + NotANumber
    			Else 'No Result'
    		End As [MightBeANumber]
    		,Case Len(Replace(Replace(Replace(Replace(Replace(NotANumber, '(', ''), ')', ''), '+', ''), ' ', ''), '-', ''))
    			When 11 Then Replace(Replace(Replace(Replace(Replace(NotANumber, '(', ''), ')', ''), '+', ''), ' ', ''), '-', '')
    			When 10 Then '0' + Replace(Replace(Replace(Replace(Replace(NotANumber, '(', ''), ')', ''), '+', ''), ' ', ''), '-', '')
    			Else 'No Result'
    		End As [ProbablyStillNotANumber]
    From	@tvTable

    However, you will probably be better served by the query presented in Naomi's link.  For your scenario:

    ;with cteCellNumbers as
    (
    	Select	NotANumber
    			,Left(	SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000),
    					PatIndex('%[^0-9.-]%', SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000) + 'X')-1) As Number
    	From	@tvTable
    )
    Select	Case Len(Number)
    			When 11 Then Number
    			When 10 Then Number
    			Else 'No Result'
    		End
    From	cteCellnUMBERS

    Your solution is compact... but it doesnt cater my needs..After removing all the spaces and other characters in that cell string, if length is less than ten (before adding zero in front of the string)... take string as is... Her is my cases...

    +0 536 373 33 9 -- should be taken as is

    +905439781632 --should be taken 05439781632

    905439781632 --should be taken 05439781632

    05439781632 --should be taken 05439781632

    5439781632 --should be taken 05439781632

    And anything else should be taken as is...


    VHO

    Thursday, November 8, 2012 3:40 PM
  • You are likely to have many different cases.  Using the CTE (which uses the query from Naomi's link), you can pull all of the numbers out of any string.  Using this, you can do whatever you need to with those numbers.  Given your last statement, though:

    ;with cteRemoveJunk as
    (
    	Select	Replace(Replace(Replace(Replace(NotANumber, '+', ''), ' ', ''), '(', ''), ')', '') As NotANumber
    	From	@tvTable
    )
    ,cteCellNumbers as
    (
    	Select	NotANumber
    			,Left(	SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000),
    					PatIndex('%[^0-9.-]%', SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000) + 'X')-1) As Number
    	From	cteRemoveJunk
    )
    Select	NotANumber
    		,Right('00000000000' + Number, 11)
    From	cteCellnUMBERS

    Thursday, November 8, 2012 4:33 PM
  • You are likely to have many different cases.  Using the CTE (which uses the query from Naomi's link), you can pull all of the numbers out of any string.  Using this, you can do whatever you need to with those numbers.  Given your last statement, though:

    ;with cteRemoveJunk as
    (
    	Select	Replace(Replace(Replace(Replace(NotANumber, '+', ''), ' ', ''), '(', ''), ')', '') As NotANumber
    	From	@tvTable
    )
    ,cteCellNumbers as
    (
    	Select	NotANumber
    			,Left(	SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000),
    					PatIndex('%[^0-9.-]%', SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000) + 'X')-1) As Number
    	From	cteRemoveJunk
    )
    Select	NotANumber
    		,Right('00000000000' + Number, 11)
    From	cteCellnUMBERS


    The ones that dont give correct results are:

    +95556214972 -should be 05556214972
    +903510278  - should be taken as is.because after removing spaces and other characters. If number of digits is less 10 exluding leading zero, then take the number as is. and it should be +903510278
    +955562149721--After removing spaces and other characters, and removing (+9) and adding a leading zero.If number of digits is more than 11 including leading zero, then take the number as is... +955562149721


    VHO

    Friday, November 9, 2012 12:36 PM
  • plz try this

    declare @thestring varchar(50) 
    set @thestring = '(0543) 978 1632' 
    declare @final varchar(50) 
    set @final = '' 
    
    select @final = @final + x.thenum 
    from 
    ( 
        select substring(@thestring, number, 1) as thenum, number 
        from master..spt_values 
        where substring(@thestring, number, 1) like '[0-9]' and type='P'
    ) x 
    order by x.number 
    set @final = LTRIM(RTRIM (@final))
    --print len (@final )
    if len (@final )>= 11
        set @final = Substring(@final,len (@final)-10, 11)
    else if len (@final )= 10
        set @final = '0'+ @final
    else
        set @final = @thestring print @final

    cases

    +95556214972 give 95556214972 since original string contains 11 digits

    +955562149721 give  55562149721 by removing +9 we are getting 11 digits

    for this you may have add cases accordingly

    Thanks

    Johnson


    • Edited by Johnson T A Friday, November 9, 2012 1:16 PM
    • Marked as answer by emmim44 Tuesday, November 13, 2012 9:21 AM
    Friday, November 9, 2012 12:57 PM
  • Given the skeleton provided, you should be able to modify to your specific needs.  At this point, I'm not sure I really follow all of your use cases.  One more attempt:

    ;with cteRemoveJunk as
    (
    	Select	Replace(Replace(Replace(Replace(Replace(Replace(NotANumber, '-', ''), '+9', ''), '+', ''), ' ', ''), '(', ''), ')', '') As NotANumber
    	From	@tvTable
    )
    ,cteCellNumbers as
    (
    	Select	NotANumber
    			,Left(	SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000),
    					PatIndex('%[^0-9.-]%', SubString(NotANumber, PatIndex('%[0-9.-]%', NotANumber), 8000) + 'X')-1) As Number
    	From	cteRemoveJunk
    )
    Select	NotANumber
    		,Case
    			When Len(NotANumber) < 10 Then NotANumber
    			When Len(Number) = 11 Then Number
    			When Len(Number) <= 10 Then Right('00000000000' + Number, 11)
    			Else 'No Result'
    		End
    From	cteCellNumbers

    Friday, November 9, 2012 2:53 PM
  • Thank you guyz for your help and support :D

    VHO

    Tuesday, November 13, 2012 9:21 AM