Remove letters while leaving numbers is a text field

[GMMurf]

I am trying to clean up some data to migrate into a SQL database. I have a text field that held phone numbers and sometimes users put ext. in the field for the extension. I want to get the ext out of all the records but leave the numbers.

Can I remove them with a blanket statement that says just remove letters not digits or do I have to remove each letter individually?

Thanks

 

[genomon]

If it's always ext., you could use a SQL UPDATE ... SET with the INSTR function.

Sure, the early bird gets the worm, but the second mouse gets the cheese in the trap.

 

[HarleyQuinn]

Or you could go all out and create a UDF that contains a regular expression replace of all non numeric characters that would handle any knid of user entered text...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

I thought I'd done something like this recently, try this without the ^a-z|

http://www.tek-tips.com/viewthread.cfm?qid=1420788

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.