Removing Charaters and leaving numbers

[dirklieske]

Hi,

I am trying to select 1234 from the following field:
XXX1234XXX

Basically I want to select the numbers and leave the characters behind any sugestions?

Also the characters may or may not be there and they may be differing lengths.

Thanks in advance.

 

[dnoeth]

A rather ugly solution is a large CASE statement:
select
(case when substring( col from 1 for 1) in ('0'..'9')
then substring( col from 1 for 1) else '' end) ||
(case when substring( col from 2 for 1) in ('0'..'9')
then substring( col from 2 for 1) else '' end) ||
(case when substring( col from 3 for 1) in ('0'..'9')
then substring( col from 3 for 1) else '' end) ....

I hope you'll do it one time only to normalize the data.

Maybe you'll just have to wait for V2R5.1 User Defined Functions ;-)

Dieter