How to get substring of numeric value

Serman D.

unread,

Apr 9, 2008, 8:42:58 AM4/9/08

to

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.

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 TERMINATE command completed successfully.

--

Lennart

unread,

Apr 9, 2008, 9:20:49 AM4/9/08

to

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

Serman D.

unread,

Apr 10, 2008, 3:22:37 AM4/10/08

to

On Apr 9, 3:20 pm, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:

> 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.

--

fyn...@googlemail.com

unread,

Apr 11, 2008, 6:42:49 AM4/11/08

to

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

Lennart

unread,

Apr 11, 2008, 2:37:07 PM4/11/08

to

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