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:

   TRANSLATE(UPPER(<your string>), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', '')

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
IBM Software Lab, Canada



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

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

replace 'abcde123' with your variable.

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
 
 [ 4 post ] 

 Relevant Pages 

1. Parse ASP search string into query string?

2. String parsing - Capitalizing each word in a string

3. String(numbers) to number

4. Parse a String to build a Hierarchy

5. Parse a String in a record

6. Parse string

7. Thank you for parse string solution!

8. Connection String Parsing

9. Function to Parse Date from String

10. String parsing in stored procedure

11. Problems with string parsing in T_SQL

12. parse string "." problem


 
Powered by phpBB® Forum Software