Extract Numeric Data from text field

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • code green
    Recognized Expert Top Contributor
    • Mar 2007
    • 1726

    #1

    Extract Numeric Data from text field

    I have a text field called notes in which users have typed almost anything they like.
    I need to find numeric data randomly inserted in there.
    So for example there may be a telephone number and an account number
    in amongst varied text.

    How do I extract this data in a SELECT query in the format
    Code:
    id       number
    xx      0107364561  2124
    OR
    Code:
    id       number
    xx      0107364561
    xx      2124
    I have found a function called ExtractInteger( )
    which returns the numeric data in the format
    Code:
    01073645612124.
    Nearly there but not quite.
    I suppose in the meantime I will try to improve on this function
    unless someone has a solution
  • Uncle Dickie
    New Member
    • Nov 2008
    • 67

    #2
    Would you be able to use a function like this to get you what you need?

    Code:
    DECLARE @Output nvarchar(50)
    SET @Output = ''
    
    WHILE patindex('%[0123456789]%', @sub) <> 0
      BEGIN
        SET @Output = @Output + substring(substring(@sub,patindex('%[0123456789]%',@sub),len(@sub)),1,patindex('%[^0123456789]%',substring(@sub,patindex('%[0123456789]%',@sub),len(@sub)))-1) + ','
        SET @sub = substring(substring(@sub,patindex('%[0123456789]%',@sub),len(@sub)),patindex('%[^0123456789]%',substring(@sub,patindex('%[0123456789]%',@sub),len(@sub))),len(@sub))
      END
    RETURN @Output
    I have used a comma to delimit the numeric parts of the @sub passed to the function

    Comment

    • Uncle Dickie
      New Member
      • Nov 2008
      • 67

      #3
      Just thought I would comment on the two lines in the WHILE statement while I remember what I did:
      1. Find the position of the 1st numeric character (using patindex)
      2. Create a substring (a1) starting from this point
      3. Search a1 for first non-numeric character (again using patindex)
      4. Create substring (a2) starting from 1st character of a1, ending 1 character before the first non-numeric


      The above 4 points are all incorporated in the first line where @Output is set

      The second line basically does the same thing but instead of creating substring a2, it creates a substring from the first non-numeric character in a1.

      This is then plugged back into the WHILE loop

      Comment

      • code green
        Recognized Expert Top Contributor
        • Mar 2007
        • 1726

        #4
        Thanks for the help.
        I need to take this project a step further now so I will post a similar question

        Comment

        • ck9663
          Recognized Expert Specialist
          • Jun 2007
          • 2878

          #5
          String parsing :) Either way, I think it'll have the same speed.

          Happy Coding!


          --- CK

          Comment

          Working...