How to split the contents of a column

[Desmodromic]

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.

[Lennart]

On Oct 2, 3:23 am, Desmodromic <[e-mail]:

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.

[pchavera]

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

[Lennart]

On Oct 4, 6:32 pm, [e-mail] 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