Select Numeric characters only

[Alfredomulet]

Hello everyone, is it there a way to select the numeric characters of a cell using just SQL?

My records:
Activity_Desc
(55) COMUNICACIONES
03-SEGUROS
ALIMENTOS Y BEBIDAS - 43
BIENES RAICES (68)
CONSTRUCCION (14 ) CARTOGRAFIA

My expected results
55
03
43
68
14

Tnxs!!!

Alfredo

 

[PHV]

In a standard code module create a function like this:

Public Function getNum(myField)
  Dim s As String, l As Long, x As String
  If Trim(myField & "") = "" Then
    getNum = myField
    Exit Function
  End If
  For l = 1 To Len(myField)
    x = Mid(myField, l, 1)
    If (x >= "0" And x <= "9") Then
      s = s & x
    End If
  Next l
  getNum = s
End Function

Then in your query:
SELECT Activity_Desc, getNum([Activity_Desc]) AS NumOnly
FROM yourTable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Alfredomulet]

Thanks PHV, unfortunatelly I've never used access modules, does it work by just saving the module as "getNum" and then running the query???
Sorry for the simple question....

 

[Golom]

Pretty much ... but you don't want to save it as "getNum" because that's the name of a Sub inside the module. Just call it "myModule" or something generic. You can add more subs and functions to it as the need arises.

 

[Alfredomulet]

tnx! it worked perfectly!