How do I extract only number from a field and store in a variable?

[chrisetiquette]

I need to extract all the numbers from a field in ms access sql query and store them in a string for future use, is this possible?

[CJ_London]

of course - look at using the val, mid, left and right functions or perhaps a combination of them

[chrisetiquette]

Here's my attempt at doing this:

SELECT Mid(Field1, 1, Len(Field1)) Like '%[0-9]%'  AS OnlyNumbers
FROM Database;

[chrisetiquette]

Tried this and it only returns a single "0" :

select val ([field1]) Like '%[0-9]%' AS OnlyNumbers
FROM Database;

[John_G]

Can you give an example of what the data in [field1] looks like?

If the numeric characters are in different places in the string with other characters between them, it might not be as easy as it looked.

[chrisetiquette]

Input:

1234 Apple Hill Road.

Output:

1234

[chrisetiquette]

Some might be like this:

Input:

Po Box 1234

Output:

1234

[jwhite]

See post #5 at http://www.utteraccess.com/forum/ind...wtopic=1952430.

Though, I am curious to know the purpose of saving this number which in your example could be a street number or a po box or something else entirely.

[chrisetiquette]

@jwhite I need to do this in a MS Access SQL query, not vb. Eventually I will use the sql statment in vb as exactly that, a sql statement.

[jwhite]

You put the code in a UDF (user defined function) and call the UDF from the sql statement...

[chrisetiquette]

@jwhite I'd rather not use a UDF if that's ok...

[InsuranceGuy]

Chris, since you're using the MS Access engine, you'll need to use a UDF. There are not built-in functions for stripping non-numeric characters from a variable/field. In other databases there are often regular expression functions that you could use (and you can actually implement regex functions using VBA UDFs), but in MS Access you can't get around using a UDF.



Jeff

[CJ_London]

if the two examples are the only situation (i.e. a number at the beginning or end, but never in the middle) you could try something like

iif(val(field1)<>0, val(field1),iif(val(strreverse(field1))<>0,strreve rse(val(strreverse(field1))) & ""),null))

[jwhite]

Public Function GetNumbersOnly(InputString As String) As String
  Dim i As Long
  For i = 1 To Len(InputString)
    If InStr("0123456789", Mid(InputString, i, 1)) > 0 Then
      GetNumbersOnly = GetNumbersOnly & Mid(InputString, i, 1)
    End If
  Next i
End Function

Then in your sql statement: GetNumbersOnly([YourFieldNameHere])