Parsing the numbers out of a string
Author Message
Parsing the numbers out of a string

Hi,

In short, I need to parse digits out of a string, eg. "KIRBDN12345" would
return 12345.

I have written the following SQL to accomplish this, I would just like to
know if anyone knows of an easier method of doing this:

select

max(A_ID),

max(

case

when
ucase(substr(A_ID,1,length(A_ID)-0))=lcase(substr(A_ID,1,length(A_ID)-0))

then substr(A_ID,1,length(A_ID)-0)

when
ucase(substr(A_ID,2,length(A_ID)-1))=lcase(substr(A_ID,2,length(A_ID)-1))

then substr(A_ID,2,length(A_ID)-1)

when
ucase(substr(A_ID,3,length(A_ID)-2))=lcase(substr(A_ID,3,length(A_ID)-2))

then substr(A_ID,3,length(A_ID)-2)

...

end

)

from

TABLE

where

A_GROUP_ID = 'XXX'

group by

A_GROUP_ID

The SQL is of course longer, so that I can parse more prefix-characters.
Note that the format of the characters is normally a six letter code
followed by a unique long integer, sometimes however there is no code at all
or only three or four letters. My goal is to determine the highest used
number for a specified group. Perhaps there is a DB2 function I am not aware
of that will make my life a little easier.

Thanks in advance for any help

Rudolf Bargholz

Sun, 09 May 2004 21:46:32 GMT
Parsing the numbers out of a string

Quote:

>  Hi,

>  In short, I need to parse digits out of a string, eg. "KIRBDN12345" would
>  return 12345.

>  I have written the following SQL to accomplish this, I would just like to
>  know if anyone knows of an easier method of doing this:

>  select
>    max(A_ID),
>    max(
>      case
>        when
>  ucase(substr(A_ID,1,length(A_ID)-0))=lcase(substr(A_ID,1,length(A_ID)-0))
>          then substr(A_ID,1,length(A_ID)-0)
>        when
>  ucase(substr(A_ID,2,length(A_ID)-1))=lcase(substr(A_ID,2,length(A_ID)-1))
>          then substr(A_ID,2,length(A_ID)-1)
>        when
>  ucase(substr(A_ID,3,length(A_ID)-2))=lcase(substr(A_ID,3,length(A_ID)-2))
>          then substr(A_ID,3,length(A_ID)-2)
>        ...
>      end
>    )

>  The SQL is of course longer, so that I can parse more prefix-characters.
>  Note that the format of the characters is normally a six letter code
>  followed by a unique long integer, sometimes however there is no code at all
>  or only three or four letters. My goal is to determine the highest used
>  number for a specified group. Perhaps there is a DB2 function I am not aware
>  of that will make my life a little easier.

That looks quite complicated to me, too.  How about the following:

Now, that is special for your scenario.  If you have special characters like
parenthesis, braces, semicolons etc., or different languages, or a
difference structure of the string like ABC123DEF456, this would produce
wrong results.

--
Knut Stolze
DB2 UDB Spatial Extender
IBM Silicon Valley Lab

Mon, 10 May 2004 11:15:32 GMT
Parsing the numbers out of a string
I would write this in SQL PL. Or, if performance is really critical as
an external UNFENCED UDF.

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development

Mon, 10 May 2004 21:40:45 GMT
Parsing the numbers out of a string

values( substr('abcde123', +1+(length(rtrim(translate('abcde123','
','0123456789'))))) )

PM

Quote:
> Hi,

> In short, I need to parse digits out of a string, eg. "KIRBDN12345" would
> return 12345.

> I have written the following SQL to accomplish this, I would just like to
> know if anyone knows of an easier method of doing this:

> select

>   max(A_ID),

>   max(

>     case

>       when
> ucase(substr(A_ID,1,length(A_ID)-0))=lcase(substr(A_ID,1,length(A_ID)-0))

>         then substr(A_ID,1,length(A_ID)-0)

>       when
> ucase(substr(A_ID,2,length(A_ID)-1))=lcase(substr(A_ID,2,length(A_ID)-1))

>         then substr(A_ID,2,length(A_ID)-1)

>       when
> ucase(substr(A_ID,3,length(A_ID)-2))=lcase(substr(A_ID,3,length(A_ID)-2))

>         then substr(A_ID,3,length(A_ID)-2)

>       ...

>     end

>   )

> from

>   TABLE

> where

>   A_GROUP_ID = 'XXX'

> group by

>   A_GROUP_ID

> The SQL is of course longer, so that I can parse more prefix-characters.
> Note that the format of the characters is normally a six letter code
> followed by a unique long integer, sometimes however there is no code at
all
> or only three or four letters. My goal is to determine the highest used
> number for a specified group. Perhaps there is a DB2 function I am not
aware
> of that will make my life a little easier.

> Thanks in advance for any help

> Rudolf Bargholz

Mon, 10 May 2004 22:15:04 GMT

 Page 1 of 1 [ 4 post ]

Relevant Pages