Home » SQL & PL/SQL » SQL & PL/SQL » SQL query
SQL query [message #354579] Mon, 20 October 2008 07:52 Go to next message
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 #354581 is a reply to message #354579] Mon, 20 October 2008 07:56 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you please tell us what you have tried so far to achieve this ?

Regards

Raj
Re: SQL query [message #354584 is a reply to message #354581] Mon, 20 October 2008 08:01 Go to previous messageGo to next message
atozvs
Messages: 12
Registered: May 2008
Location: Hyderabad
Junior Member
I have tried using INSTR and SUBSTR functions.
but not gettting the desired output. Sad
Re: SQL query [message #354596 is a reply to message #354584] Mon, 20 October 2008 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Copy and paste what you tried.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: SQL query [message #355306 is a reply to message #354596] Thu, 23 October 2008 06:47 Go to previous messageGo to next message
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 #355311 is a reply to message #354579] Thu, 23 October 2008 07:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


SPLIT STRING

may give you a good start your are looking for .

Smile
Rajuvan.
Re: SQL query [message #355316 is a reply to message #355306] Thu, 23 October 2008 07:43 Go to previous message
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

Previous Topic: types
Next Topic: Comparing similar strings in a table
Goto Forum:
  


Current Time: Thu Nov 14 05:51:38 CST 2024