Find all Numeric Characters in a string

[silversurfer19]

Hi, I'm not very experienced with SQL at all and am attempting to create a query which will allow me to select all numeric characters from a specific field of every record in a table, regardless of how many numeric characters there are, or the order they are in. The Field being examined is an address field and essentially all I want to do is remove the number, such as 21, from 21 High Street etc... I could just select the two left-most characters from the string, but it doesn't seem a very safe solution and there other addresses such as Unit 6, and I would like the number 6 from that also, and I don't know how to go about getting both.

Thanks in advance,
any help much appreciated.

 

[boblarson]

You could use something like this function (put it into a Standard Module and name the module something other than the function name):

Function StripNums(strInput As String) As String
' returns string without numeric characters
' Usage in a query:
'   MynewFieldName: StripNums([YourFieldNameHere])
 
    Dim strHold As String
    Dim lngL As Long
    Dim strNewString As String
 
    If Len(strInput) > 0 Then
 
        lngL = 1
 
        Do Until lngL = Len(strInput)
 
            strHold = Mid(strInput, lngL, 1)
 
            If Not IsNumeric(strHold) Then
                strNewString = strNewString & strHold
            End If
 
            lngL = lngL + 1
 
        Loop
 
        StripNums = strNewString
 
    Else
 
        StripNums = "N/A"
 
    End If
 
End Function

 

[silversurfer19]

Thanks for the reply, it sounds like something that should be useful, unfortunately I've only worked with SQL in Access before and never used functions or modules before. I've looked about online but nothing is particularly clear about exactly how they work. Could you possibly explain to me exactly how I'd run this function (or module) in relation to my table data?

Thanks again for help so far

 

[silversurfer19]

Ok, I've been playing around with the query, and think I've managed to create a module and run it using the commented line at the top of the code you gave me, which has run properly. Unfortunately it's not doing what I need it to do, I need to view only the numbers which are present in the String, whereas your code is stripping out the numbers (and in some cases some text values from the string as well) and presenting me with whats left. Is there any way to do a similar thing but remove only text characters and not numbers?

 

[silversurfer19]

After a bit of fiddling around I've got the query running and providing only the numbers present in the string. Or blank if there are no numbers. Unfortunately I am now having a problem whereby Records which contained nothing in that particular field are returning an #Error Message, as far as I can tell because the Function can't cope with null input. This is causing me a lot of trouble because I need to be able to run other queries with the input. Could anyone suggest a means of solving this problem please?

Thanks

 

[JANR]

Function StripNums(strInput As [COLOR=red]Variant[/COLOR]) As String

Declare strInput as Variant, since it can handle Nulls.

JR

 

[boblarson]

Function StripNums(strInput As [COLOR=red]Variant[/COLOR]) As String

Declare strInput as Variant, since it can handle Nulls.

JR

I would use NZ instead of declaring a variant. Variants are not as efficient as strong typing.

 

[silversurfer19]

Thanks very much, it works perfectly now.

 

[gemma-the-husky]

i know you have it fixed, but if the number comes FIRST then simply

val(mystring) will give the numeric value of the numbers up to the first non-numeric character.

 

[Brianwarnock]

i know you have it fixed, but if the number comes FIRST then simply

val(mystring) will give the numeric value of the numbers up to the first non-numeric character.

He does say in the first post that the numbers can be anywhere.

Brian