Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    29

    Unanswered: Need to Strip non-numerics from phone field

    I tried searching forums for the last hour and could not find much.

    Rather then doing...

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(ISNULL(PHONE, ''), ' ', ''), '(', ''), ')', ''), '-', ''), '.', ''), ':', ''), '+', '')

    I was hoping there was an equally efficient alternative that utilizes PATINDEX('%[^0-9]%', PHONE)

    to some capacity (i.e. uses regular expressions to strip all non-numeric characters from the phone field)

    I am certain this has been addressed before, just not sure if the "REPLACE to the nth degree" is the only solution.

    THANKS!

  2. #2
    Join Date
    Nov 2003
    Posts
    29
    Any way to make this recursive based on the actual length in the field? Or would I take a performance hit?

    SELECT
    CASE WHEN SUBSTRING( PHONE , 1 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 1 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE , 2 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 2 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE , 3 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 3 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE , 4 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 4 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE , 5 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 5 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE , 6 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 6 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE , 7 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 7 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE , 8 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 8 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE , 9 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 9 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,10 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,10 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,11 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,11 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,12 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,12 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,13 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,13 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,14 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,14 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,15 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,15 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,16 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,16 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,17 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,17 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,18 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,18 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,19 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,19 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,20 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,20 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,21 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,21 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,22 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,22 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,23 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,23 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,24 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,24 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,25 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,25 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,26 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,26 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,27 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,27 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,28 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,28 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,29 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,29 , 1 ) ELSE '' END +
    CASE WHEN SUBSTRING( PHONE ,30 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,30 , 1 ) ELSE '' END
    AS NUMERICVALUEONLY
    FROM COUNCIL_MEMBER

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use one of my old handy-dandys that I wrote for just this kind of job!
    Code:
    CREATE FUNCTION dbo.fNumeric(@pcIn VARCHAR(100)) RETURNS VARCHAR(100) AS
    BEGIN
    DECLARE @i		INT
    
    SET @i = PatIndex('%[^0-9]%', @pcIn)
    
    WHILE 0 < @i
       BEGIN
          SET @pcIn = Stuff(@pcIn, @i, 1, '')
          SET @i = PatIndex('%[^0-9]%', @pcIn)
       END
    
    RETURN @pcIn
    END
    GO
    -PatP

  4. #4
    Join Date
    Nov 2003
    Posts
    29
    Quote Originally Posted by Pat Phelan
    I'd use one of my old handy-dandys that I wrote for just this kind of job!
    Code:
    CREATE FUNCTION dbo.fNumeric(@pcIn VARCHAR(100)) RETURNS VARCHAR(100) AS
    BEGIN
    DECLARE @i		INT
    
    SET @i = PatIndex('%[^0-9]%', @pcIn)
    
    WHILE 0 < @i
       BEGIN
          SET @pcIn = Stuff(@pcIn, @i, 1, '')
          SET @i = PatIndex('%[^0-9]%', @pcIn)
       END
    
    RETURN @pcIn
    END
    GO
    -PatP
    While it looks pretty, it doesn't look very efficient - it does give me an idea though, I'll post back if I have a more elegant solution then my last post.

  5. #5
    Join Date
    Nov 2003
    Posts
    29
    Something like this might work if I packaged it into a UDF, not sure how much of a performance hit I would take with the While loop (I know very similar to what Pat posted - ok nearly identical)

    DECLARE @phone VARCHAR(100)
    SELECT @phone = '123ab 34 902x:a123ab 34 902x:a'

    WHILE (PATINDEX('%[^0-9]%', @phone) > 0)
    SELECT @phone = REPLACE(@phone, SUBSTRING(@phone, PATINDEX('%[^0-9]%', @phone), 1), '')

    SELECT @phone

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The only difference is he gets Estimated Row Size of 61 while yours holds at 4 regardless of the string length and contents.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    The only difference is he gets Estimated Row Size of 61 while yours holds at 4 regardless of the string length and contents.
    I'm not sure that I understand the significance of that comment, but Ok. The PatIndex() call is (or at least was when I wrote this) expensive in terms of both time and resources consumed, so I was trying to minimize those calls.

    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Nothing significant, Pat, I was just trying to identify the differences, that's all
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •