By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

How to split the contents of a column

P: n/a
The SQL statement below returns the single value "8000k/1000k".

select col1 from table1 fetch first 1 row only for read only with ur;

COL1
----------------
8000k/1000k

Now, this is actually two values held in the same column (horrible
database design, I know).

What I am trying to write is an SQL statement that will split this
single column (varchar(16)) and return two integer values - 8000 and
1000. So, the field delimiter is '/' and I want to strip off the 'k'
characters.

Any idea how I can achieve this with SQL (DB2 v8).

Thanks.

Oct 2 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Oct 2, 3:23 am, Desmodromic <davies...@yahoo.com.auwrote:
The SQL statement below returns the single value "8000k/1000k".

select col1 from table1 fetch first 1 row only for read only with ur;

COL1
----------------
8000k/1000k

Now, this is actually two values held in the same column (horrible
database design, I know).

What I am trying to write is an SQL statement that will split this
single column (varchar(16)) and return two integer values - 8000 and
1000. So, the field delimiter is '/' and I want to strip off the 'k'
characters.

Any idea how I can achieve this with SQL (DB2 v8).
As usual it depends :-) If you can assume that there is always 2
values, and that they always end with a letter you can use something
like:

db2 "with T (c) as ( values '8000k/1000k' ) select c, substr(c,
1,locate('/',c)-2), substr(c,locate('/',c)+1,
length(substr(c,locate('/',c)+1)) - 1) from T"

C 2 3
----------- ----------- -----------
8000k/1000k 8000 1000

1 record(s) selected.

If you are going to use it a lot, stuff it in a function or a view. As
you noticed your self, the design of the table has improvement
potential :-)
HTH
/Lennart

Thanks.

Oct 2 '07 #2

P: n/a
hi!...i'm trying to use substr(c,locate('/',c)+2) in DB2 ..but DB2
show me this error message : unautorizated name "LOCATE" ..are you sur
that is for DB2?????...
thanks

Oct 4 '07 #3

P: n/a
On Oct 4, 6:32 pm, pchav...@gmail.com wrote:
hi!...i'm trying to use substr(c,locate('/',c)+2) in DB2 ..but DB2
show me this error message : unautorizated name "LOCATE" ..are you sur
that is for DB2?????...
What version of db2 are you using, what's you query, and what error
message are you getting?

/Lennart
Oct 4 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.