GENERAL SQL Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mghale - 11 Apr 2006 21:53 GMT

A client gave a spreadsheet that has this column format:

.u29034c.ccridl.djkcjd

I need to parse out only the characters between the first two periods
in the column using only SQL.

Any suggestions?

DB2 8.2 on AIX

Thanks in advance!

Martin

Reply to this Message

tuarek - 11 Apr 2006 23:31 GMT

create user defined function in C/ C++ or java to parse the string?

regards,

mehmet

Reply to this Message

tuarek - 11 Apr 2006 23:33 GMT

You can use UDFs. Create an user defined function in C/C++ or java to
parse the string? Register it in the database.

Then you would be able to call it in the SQL stmts.

Regards,

Mehmet

Reply to this Message

Gert van der Kooij - 12 Apr 2006 00:04 GMT

> A client gave a spreadsheet that has this column format:
>
[quoted text clipped - 10 lines]
>
> Martin

drop table t1;

create table t1 (c1 char(40));

insert into t1 (c1) values ('.u29034c.ccridl.djkcjd'),
('.gfovfgpt.cdft.fdfgrt');

select substr(c1,posstr(c1,'.') + 1,
posstr(substr(c1,posstr(c1,'.') + 1), '.')
- posstr(c1,'.') )
from t1;

1
----------------------------------------
u29034c
gfovfgpt

2 record(s) selected.

Reply to this Message

Tonkuma - 12 Apr 2006 09:07 GMT

A little generalization.
I'm afraid real data would not include following data(first period is
not in first position.)
Example: ('alpha.gfovfgpt.cdft.fdfgrt')

But, if there are such data, this would be better.
------------------- Commands Entered -------------------------
select C1
, substr(c1,posstr(c1,'.') + 1,
posstr(substr(c1,posstr(c1,'.') + 1), '.') -1)
from t1;
--------------------------------------------------------------------

C1 2
---------------------------------------- ------------------------------
.u29034c.ccridl.djkcjd u29034c

.gfovfgpt.cdft.fdfgrt gfovfgpt

alpha.gfovfgpt.cdft.fdfgrt gfovfgpt

3 record(s) selected.

Reply to this Message

mghale - 12 Apr 2006 14:18 GMT

Thank you all for your postings. That was exactly the information I
needed!!

Martin

Reply to this Message