467,209 Members | 1,352 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,209 developers. It's quick & easy.

GENERAL SQL Question

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

Apr 11 '06 #1
  • viewed: 1093
Share:
5 Replies
create user defined function in C/ C++ or java to parse the string?

regards,

mehmet

Apr 11 '06 #2

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

Apr 11 '06 #3
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.
Apr 11 '06 #4
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.

Apr 12 '06 #5
Thank you all for your postings. That was exactly the information I
needed!!

Martin

Apr 12 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by JezB | last post: by
2 posts views Thread by ZorpiedoMan | last post: by
105 posts views Thread by Christoph Zwerschke | last post: by
1 post views Thread by Mark Fink | last post: by
3 posts views Thread by =?Utf-8?B?Ymxi?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.