Remove numeric characters...

[Neil Toulouse]

Hi guys!

I am assuming this is an easy one! Basically I am trying to strip any numeric character (ie 0 - 9) from a charater field. I am currently using this long-winded code:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([MyField],'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','') AS MyField

..but is there a better/shorter way?

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[mm0000]

replace myfield with chrtran(myfield,'123456789','')

 

[mm0000]

Sorry missed out the 0


replace myfield with chrtran(myfield,'0123456789','')

 

[mm0000]

Sorry wrong forum

 

[Neil Toulouse]

lol

It is a conversion of a VFP process to a SQL stored procedure, so not too far off the mark!

I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

I blogged about this over a year ago.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-extracting-data-again

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Neil Toulouse]

thanks George

I like work. It fascinates me. I can sit and look at it for hours...

 

[markros]

And if you want VFP style functions in SQL Server, check this blog by Brad Schulz
Handy String Functions

PluralSight Learning Library