SQL query [message #354579] |
Mon, 20 October 2008 07:52 |
atozvs
Messages: 12 Registered: May 2008 Location: Hyderabad
|
Junior Member |
|
|
Hi
I have a table where we have a column called Names.
Names
---------------
sachin ramesh tendulkar
Rahul Dravid
V V S Laxman
Now I want the output in the format
--------------
S.R.Tendulkar
R.Dravid
V.V.S.Laxman
Can you please help me how to get this output using SQL query.
thanks
VS
|
|
|
|
|
|
Re: SQL query [message #355306 is a reply to message #354596] |
Thu, 23 October 2008 06:47 |
atozvs
Messages: 12 Registered: May 2008 Location: Hyderabad
|
Junior Member |
|
|
This is the script for my query.
create table names
(name varchar2(20));
insert into names values('Sachin Ram Tendulkar');
insert into names values('Rahul Dravid');
insert into names values('V V S Laxman');
commit;
when I used the below query
SQL> select substr(name, 1,1)||'.'||
substr(name, instr(name,' ',-1)+1) Name
from names;
i got the below output.
Name
-----------------------
R.Dravid
V.Laxman
S.Tendulkar
But I was not able to proceed the further. Can any one please help me.
Regards
VS.
|
|
|
|
Re: SQL query [message #355316 is a reply to message #355306] |
Thu, 23 October 2008 07:43 |
|
Michel Cadot
Messages: 68712 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> with
2 data as (
3 select rownum id, name,
4 substr(' '||name,1,instr(name,' ',-1)+1) first_names,
5 substr(name, instr(name, ' ',-1)+1) last_name
6 from names
7 ),
8 split as (
9 select distinct id, name, last_name, level,
10 substr(upper(
11 substr(first_names,
12 instr(first_names,' ',1,level)+1,
13 instr(first_names,' ',1,level+1)-instr(first_names,' ',1,level)))
14 ,1,1)||'.' ini
15 from data
16 connect by instr(first_names,' ',1,level) > 0
17 order by id, level
18 )
19 select name, replace(wm_concat(ini),',','')||last_name name
20 from split
21 where ini != '.'
22 group by id, name, last_name
23 /
NAME NAME
-------------------- --------------------
Sachin Ram Tendulkar S.R.Tendulkar
Rahul Dravid R.Dravid
V V S Laxman V.V.S.Laxman
3 rows selected.
Regards
Michel
[Updated on: Thu, 23 October 2008 07:51] Report message to a moderator
|
|
|