Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sql to extract numeric only (Access 2003)

    Sample data:
    (Text Field)
    ABC123456
    123ABCDEF456
    0123ABCD456
    1234560ABCDE

    We are looking to extract the numeric portion of the above.
    We do not want leading zeros
    We DO WANT trailing zeros.

    The character count may be different for all data.

    Based on the above, we would want the following results:
    123456
    123456
    123456
    1234560

    Is there SQL code that can do this?

    I've tried using MID but am having a difficult time with it.

    Thank you for helping !

    Michael

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sql to extract numeric only (Access 2003)

    I think you need a custom VBA function for this:
    <code>
    Public Function ExtractNumber(strText As String) As Long
    Dim strRet As String
    Dim i As Integer
    Dim c As String
    For i = 1 To Len(strText)
    c = Mid(strText, i, 1)
    If c Like "<!t>[0-9]<!/t>" Then
    strRet = strRet & c
    End If
    Next i
    ExtractNumber = CLng(strRet)
    End Function
    </code>
    You can use this in a query as ExtractNumber([FieldName]), either as a calculated column in a select query or in the Update to line of an update query.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sql to extract numeric only (Access 2003)

    Hi Hans,

    I can't wait to get back to work Monday to try it out !

    Thanks so much and have a nice weekend !!

    Michael

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sql to extract numeric only (Access 2003)

    Hi Hans,
    I tried it using the data above and of course it works perfectly.

    There was one scenario that I did not plan on.

    If the field only has alpha characters, the update query gives a Run time 13 error Type mismatch.

    Sample data:
    (Text Field)
    ABC123456
    123ABCDEF456
    0123ABCD456
    1234560ABCDE
    ABCDEFG <----------- bombs out here

    (Highlights this line of code: ExtractNumber = CLng(strRet)

    Is there a way around this?

    Thanks again for looking and assisting.

    Sincerely,
    Michael

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sql to extract numeric only (Access 2003)

    What would you like the function to return in that situation - 0 (zero) or null (blank)?

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sql to extract numeric only (Access 2003)

    Null would be perfect.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sql to extract numeric only (Access 2003)

    Try this version:
    <code>
    Public Function ExtractNumber(strText As String) As Variant
    Dim strRet As String
    Dim i As Integer
    Dim c As String
    For i = 1 To Len(strText)
    c = Mid(strText, i, 1)
    If c Like "<!t>[0-9]<!/t>" Then
    strRet = strRet & c
    End If
    Next i
    If strRet = "" Then
    ExtractNumber = Null
    Else
    ExtractNumber = CLng(strRet)
    End If
    End Function
    </code>
    Note that the return type is now a Variant - this is necessary if you want to be able to return Null values.

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sql to extract numeric only (Access 2003)

    Hans,

    Once again you did it. You are certainly a very nice man for sharing your vast knowledge with us.

    I thank you and appreciate your generosity.

    Michael

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sql to extract numeric only (Access 2003)

    If there is a chance that the argument to the function will be null (blank), you can change the function to
    <code>
    Public Function ExtractNumber(varText As Variant) As Variant
    Dim strRet As String
    Dim i As Integer
    Dim c As String
    If IsNull(varText) Then
    ExtractNumber = Null
    Exit Function
    End If
    For i = 1 To Len(varText)
    c = Mid(varText, i, 1)
    If c Like "<!t>[0-9]<!/t>" Then
    strRet = strRet & c
    End If
    Next i
    If strRet = "" Then
    ExtractNumber = Null
    Else
    ExtractNumber = CLng(strRet)
    End If
    End Function</code>

Posting Permissions

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