Sql to extract numeric only (Access 2003)

Topic

New Reply

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

Reply | Quote

Replies

I think you need a custom VBA function for this:

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 "[0-9]" Then
strRet = strRet & c
End If
Next i
ExtractNumber = CLng(strRet)
End Function

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Null would be perfect.

Reply | Quote

Try this version:

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 "[0-9]" Then
strRet = strRet & c
End If
Next i
If strRet = "" Then
ExtractNumber = Null
Else
ExtractNumber = CLng(strRet)
End If
End Function

Note that the return type is now a Variant – this is necessary if you want to be able to return Null values.

Reply | Quote

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

Reply | Quote

If there is a chance that the argument to the function will be null (blank), you can change the function to

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 "[0-9]" Then
strRet = strRet & c
End If
Next i
If strRet = "" Then
ExtractNumber = Null
Else
ExtractNumber = CLng(strRet)
End If
End Function

Reply | Quote