CODEWITHSUNDEEP
sqlms-access
DasPete
DasPete

Reputation: 851

SQL remove text, leave number

I have some old data in an access database that is very poorly formated. There is a packSize column with mixed data. Sometimes it says "2 pack", sometimes "4 pk", sometimes "3", etc. What I would like to do is run an SQL command to remove all the text and leave just the number. How should I go about this?

Upvotes: 0

Views: 2433

Answers (1)

sgeddes
sgeddes

Reputation: 62831

While the VAL command can work if the field always starts with numeric values, this may not always be the case. Sample usage:

UPDATE Table SET field = Val(field)

If you want to strip out all non-numeric values from a string/field, this is a method I've used in the past (directly from Microsoft):

Function RemoveAlphas (ByVal AlphaNum as Variant)

   Dim Clean As String
   Dim Pos, A_Char$

   Pos = 1
   If IsNull(AlphaNum) Then Exit Function

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ >= "0" And A_Char$ <= "9" Then
         Clean$ = Clean$ + A_Char$
      End If
   Next Pos

   RemoveAlphas = Clean$

End Function

And:

How to Use the RemoveAlphas() Function in an Update Query

Create a new query based on the table with the phone number field. Place the phone number field in the first column of the query grid. On the Query menu, click Update. In the Update To row, enter the following for a field named Phone: RemoveAlphas([Phone]) Run the query.

http://support.microsoft.com/kb/210537

Upvotes: 2

Related Questions