469,315 Members | 1,795 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

How to get substring of numeric value

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.

--
Apr 9 '08 #1
4 23346
On Apr 9, 2:42 pm, "Serman D." <serma...@hotmail.comwrote:
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.

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

Apr 9 '08 #2
On Apr 9, 3:20 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
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.

--
Apr 10 '08 #3
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
Apr 11 '08 #4
On Apr 10, 9:22 am, "Serman D." <serma...@hotmail.comwrote:
On Apr 9, 3:20 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
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.

--
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
Apr 11 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Iona | last post: by
3 posts views Thread by eric_caron_31 | last post: by
13 posts views Thread by nishit.gupta | last post: by
1 post views Thread by karthik2423 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.