How to get substring of numeric value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Serman D. - 09 Apr 2008 13:42 GMT

I would like to extract the first 6 digits of a numeric value (e.g.
the string '123456' out of the numeric 1234567890123456789). I tried
a combination of CAST and SUBSTR, but it seems I am not doing the
conversion properly. Please advice:

$ cat substr.sql
connect to viper
@

create table largenum (id integer not null, lval decimal(21,0) not
null)
@

insert into largenum values (1, 1234567890123456789)
@

select lval from largenum
@

select substr(cast(lval as char), 1, 6) from largenum
@

drop table largenum
@

connect reset
@

terminate
@

$ db2 -td@ -f substr.sql

Database Connection Information

Database server = DB2/LINUX 9.5.0
SQL authorization ID = DB2INST3
Local database alias = VIPER

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

LVAL
-----------------------
1234567890123456789.

1 record(s) selected.

SQL0138N A numeric argument of a built-in string function is out of
range.
SQLSTATE=22011

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB20000I The TERMINATE command completed successfully.

--

Reply to this Message

Lennart - 09 Apr 2008 14:20 GMT

> I would like to extract the first 6 digits of a numeric value (e.g.
> the string '123456' out of the numeric 1234567890123456789). I tried
[quoted text clipped - 56 lines]
>
> --

You need to cast it to something bigger than a single char. I.e.:

db2 "select substr(cast(lval as char(22)),3,6) from largenum"

1
------
123456

1 record(s) selected.

/Lennart

Reply to this Message

Serman D. - 10 Apr 2008 08:22 GMT

> db2 "select substr(cast(lval as char(22)),3,6) from largenum"

Thank you for your replay Lennart. I have some follow-up questions:

1. Why are leading zeros added in the cast from numeric to char?
2. How do I reliably remove them?
3. Consider the below sample: What query returns the string '123456'
for the all rows?

create table largenum (lval decimal(21,0) not null)
@

insert into largenum values
(1234567890123456789),
( 123456789012345678),
( 12345678901234567),
( 1234567890123456),
( 123456789012345),
( 12345678901234),
( 1234567890123),
( 123456789012)
@

select substr(cast(lval as char(64)), 3, 6) as as_char from largenum
@

AS_CHAR
-------
123456
012345
001234
000123
000012
000001
000000
000000

8 record(s) selected.

--

Reply to this Message

fynn00@googlemail.com - 11 Apr 2008 11:42 GMT

your previous code indicates you are on Version 9

> Database server = DB2/LINUX 9.5.0
> SQL authorization ID = DB2INST3
> Local database alias = VIPER

Did you try the STRIP function already?

db2 "values(STRIP('00123',L,'0'))"

1
-----
123

Regards,
Florian

Reply to this Message

Lennart - 11 Apr 2008 19:37 GMT

> > db2 "select substr(cast(lval as char(22)),3,6) from largenum"
>
[quoted text clipped - 36 lines]
>
> --

Something like:

select substr(cast(lval as char(22)), 22 - length(strip(cast(lval as
char(22)), L, '0')) + 1, 6) as as_char from largenum"

AS_CHAR
-------
123456
123456
123456
123456
123456
123456
123456
123456

/Lennart

Reply to this Message