GENERAL SQL Question

[mghale]

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

[tuarek]

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

regards,

mehmet

[tuarek]

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

[Gert van der Kooij]

In article <11**********************@g10g2000cwb.googlegroups .com>,
ma*********@yahoo.com says...

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

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.

[Tonkuma]

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.

[mghale]

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

Martin