Excellent !!!

Here the equivalent for Oracle:
SELECT
REPLACE(TRANSLATE(UPPER('YOUR_STRING'),
'0123456789
ABCDEFGHIJKLMNOPQRSTUVWXYZ,
./;''[]\`~!@#$%^\*()__+{}-|?><',
'0123456789'
), ' ', '') FROM DUAL;

Again many thanks,
Regis

-----Original Message-----
From: Schlieper, Paul [mailto:paul.[Email Address Removed]
Sent: 02 February 2004 14:49
To: Regis Biassala; LazyDBA.com Discussion
Subject: RE: Remove non-numeric characters

Regis,

Put something like this into a user function:


DECLARE @cString VARCHAR(32)
DECLARE @nPos INTEGER
SELECT @cString = '90$%45623 *6%}~:@'
SELECT @nPos = PATINDEX('%[^0-9]%', @cString)

WHILE @nPos > 0
BEGIN
SELECT @cString = STUFF(@cString, @nPos, 1, '')
SELECT @nPos = PATINDEX('%[^0-9]%', @cString)
END

SELECT @cString



-----Original Message-----
From: Regis Biassala [mailto:Regis.[Email Address Removed] Monday, February 02, 2004 9:28 AM
To: LazyDBA.com Discussion
Subject: Remove non-numeric characters


Hi Gurus,

I am looking for a sample of code that does exactly what translate would do
in Oracle.

Basically I am looking for a function that would remove non-numeric
characters within a string



Example:

90$%45623 *6%}~:@ should become: 90456236



Thanks,

Regis

*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.

This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.

This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************

MS Sql Server LazyDBA home page