strip numeric characters from a text field

P

Prashant Rao

How can I build a update query that removes all numeric characters in a
field? I have a field that has data such as "3478 PRG Management", "Asc
Chiti Baa", "54 Oracle DBA"... they have from 0 to 6 leading numeric
characters, using the Left function with " " as the separator will not work
since some of the fields do not have numeric chars at all. Please help.
Thanks

PR
 
J

Jeff Boyce

An inelegant (i.e., 'brute force') might be to use VBA code to step through
the string, one character at a time, looking for digits and modifying the
string to leave each one out.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

Jerry Whittle

Ugly. Hopefully someone finds a better solution.

IIF(Val("3478 PRG Management")>0,Trim(Mid("3478 PRG Management",
Len(CStr(Val("3478 PRG Management")))+1)), "3478 PRG Management")
 
J

John Spencer

Assumptions:
--The leading numbers are all you want to remove
-- Leading numbers are always followed by one or more spaces and
-- If there are leading numbers there are no letters before the first space

UPDATE YourTable
SET YourField = Mid([Your Field, Instr(1,YourField," ")+1)
WHERE YourField Like "#* *"

BACKUP your data before you run the update. You cannot undo an update
query.

You can see the results by using a SELECT query to "preview" what you will
get

SELECT YourField
, Mid([Your Field, Instr(1,YourField," ") +1) as ProposedValue
FROM YourTable
WHERE YourField Like "#* *"



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
P

Prashant Rao

Thanks, this worked beautifully!!

Jerry Whittle said:
Ugly. Hopefully someone finds a better solution.

IIF(Val("3478 PRG Management")>0,Trim(Mid("3478 PRG Management",
Len(CStr(Val("3478 PRG Management")))+1)), "3478 PRG Management")
 
J

John Spencer

Glad that solution is working although if you should have a value like
"001 Occam Blast" it would fail. Since the Val of that is "1" the
truncation of the field would return "01 Occam Blast".

Probably the only way to really handle this well would be to write a
custom function or to use regular expressions
See John Nurick's article at

http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top