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.

On Oct 2, 3:23 am, Desmodromic <davies... (AT) yahoo (DOT) com.au> wrote:
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

On Oct 2, 12:33 pm, Lennart <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:
Lennart,

Thanks for putting me on the right track. Actually, the data doesn't
always include the 'k' character. I have found the following does
exactly what I want.

select distinct speed,
int(replace(substr(speed,1,locate('/', speed)-1),'k','')) download,
int(replace(substr(speed,locate('/', speed)+1),'k','')) upload
from table1
order by download desc, upload desc
for read only with ur

Regards,

/M

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

On Oct 4, 6:32 pm, pchav... (AT) gmail (DOT) com wrote:
What version of db2 are you using, what's you query, and what error
message are you getting?

/Lennart