replace all non-numeric characters with nothing?

[mlager]

I have a situation where I have a bunch of fields like this:

ZX827256BC
XYZ90393SD
CBZ933II

What I really want to do is find a function that just strips out non-numeric characters, leaving me with just the numbers.

Is this possible?

 

[philhege]

I suppose you could functionalize this quick-and-dirty code:

set @myval = 'SD95uik78493ABC'--parameter for the func
set @myreturnval = ''

while len(@myval) > 0
begin
if isnumeric(left(@myval,1)) > 0
set @myreturnval = @myreturnval + left(@myval,1)
set @myval = case when len(@myval) > 1 then substring(@myval,2,len(@myval))
else '' end
end
select @myreturnval

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[mlager]

This is great, that's exactly what I needed, I've learned a lot from this and I appreciate your help.

Matt

 

[philhege]

I'm not entirely sure this is the most efficient (or correct) solution; I just threw it together while I was watching pre-season football last night. Glad to be of help, though.

To test, put a number in the last position of the string. I think you'll need to tweak the last part.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.