replacing numbers in a string

cole · April 1st, 2007, 02:38 PM

I'm looking for a quick solution to replace numeric values with a # within a string. The numbers can fall anywhere within the string.
i.e.
123text
1text23
12te3xt

I was hoping there was a function like replace(textname, isnumeric(textname), '#')

Or do I have to loop thru each and every character in the textname to analyze if it is numeric or not.

Help is appreciated.

happygv · April 2nd, 2007, 01:52 AM

IMO, you got to loop through the string and replace it. Or the other way would be to have 10 REPLACE function performed on the string.

Code:

SELECT @VarName = textname from TABLE_NAME ... WHERE ColumnName = Somevalue
Select @VarName = Replace(@VarName, '0', '#')
Select @VarName = Replace(@VarName, '1', '#')
...
...
Select @VarName = Replace(@VarName, '9', '#')

Cheers.

_________________________
- Vijay G
Strive for Perfection

Peso · April 2nd, 2007, 04:47 AM

Take a look at these functions found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083

happygv · April 2nd, 2007, 10:01 AM

Hmmm, That looks a good solution. Good effort.

Cheers.

_________________________
- Vijay G
Strive for Perfection

cole · April 2nd, 2007, 04:24 PM

thanks, I figured there was no other way.
regards,
C