Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve first letter of every word from string (Windows XP/ 7)
| How to retrieve first letter of every word from string [message #597433] |
Fri, 04 October 2013 11:44  |
 |
dark_prince
Messages: 121
Registered: June 2013
Location: India
|
Senior Member |
|
|
Hii, I want to retrieve first letter of every word from the string.
For ex. if string is "Computer Science Engineering (New)" then I want "CSE" or if string is "Mechanical Engineering" then "ME".
Can anyone tell me how to do this..
|
|
|
|
|
|
|
|
|
|
| Re: How to retrieve first letter of every word from string [message #597446 is a reply to message #597445] |
Fri, 04 October 2013 12:15  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
In this version solution is:
SQL> with
2 data as (
3 select 'Mechanical Engineering' val from dual
4 union all
5 select 'Computer Science Engineering (New)' from dual
6 ),
7 find_initials as (
8 select val, substr(val,instr(' '||val,' ',1,column_value),1) i
9 from data,
10 table(cast(multiset(select level word from dual
11 connect by level <= length(val)-length(replace(val,' ',''))+1
12 ) as sys.odcinumberlist))
13 where substr(val,instr(' '||val,' ',1,column_value),1) != '('
14 )
15 select val, replace(stragg(i),',') initials
16 from find_initials
17 group by val
18 order by val
19 /
VAL INITIALS
---------------------------------- ----------
Computer Science Engineering (New) CSE
Mechanical Engineering ME
"stragg" is T. Kyte's function you will find on AskTom.
|
|
|
|
|
|
 Re: How to retrieve first letter of every word from string [message #597448 is a reply to message #597446] |
Fri, 04 October 2013 12:24 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
And in 11.2, you can use the regexp and listagg functions:
SQL> with
2 data as (
3 select 'Mechanical Engineering' val from dual
4 union all
5 select 'Computer Science Engineering (New)' from dual
6 ),
7 find_initials as (
8 select val, column_value,
9 substr(regexp_substr(val||' ','[^ ]* ',1,column_value),1,1) i
10 from data,
11 table(cast(multiset(select level word from dual
12 connect by level <= regexp_count(val,' ')+1
13 ) as sys.odcinumberlist))
14 where substr(val,instr(' '||val,' ',1,column_value),1) != '('
15 )
16 select val, replace(listagg(i) within group (order by column_value),',') initials
17 from find_initials
18 group by val
19 order by val
20 /
VAL INITIALS
---------------------------------- ----------
Computer Science Engineering (New) CSE
Mechanical Engineering ME
|
|
|
|
|
|
| Re: How to retrieve first letter of every word from string [message #597452 is a reply to message #597448] |
Fri, 04 October 2013 12:35 |
|
dark_prince
Messages: 121
Registered: June 2013
Location: India
|
Senior Member |
|
|
Michel Cadot wrote on Sat, 05 October 2013 00:24
And in 11.2, you can use the regexp and listagg functions:
SQL> with
2 data as (
3 select 'Mechanical Engineering' val from dual
4 union all
5 select 'Computer Science Engineering (New)' from dual
6 ),
7 find_initials as (
8 select val, column_value,
9 substr(regexp_substr(val||' ','[^ ]* ',1,column_value),1,1) i
10 from data,
11 table(cast(multiset(select level word from dual
12 connect by level <= regexp_count(val,' ')+1
13 ) as sys.odcinumberlist))
14 where substr(val,instr(' '||val,' ',1,column_value),1) != '('
15 )
16 select val, replace(listagg(i) within group (order by column_value),',') initials
17 from find_initials
18 group by val
19 order by val
20 /
VAL INITIALS
---------------------------------- ----------
Computer Science Engineering (New) CSE
Mechanical Engineering ME
I want to use this concept in Oracle Forms as string at a time so will you tell me how to use this in procedure
|
|
|
|
|
|
|
|
|
|
|
|
 Re: How to retrieve first letter of every word from string [message #597464 is a reply to message #597460] |
Fri, 04 October 2013 14:42 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> create or replace function get_initials (p_in varchar2) return varchar2
2 is
3 result varchar2(100);
4 begin
5 select replace(stragg(i),',') into result
6 from ( select substr(p_in,instr(' '||p_in,' ',1,column_value),1) i
7 from table(cast(multiset(
8 select level word from dual
9 connect by level <= length(p_in)-length(replace(p_in,' ',''))+1
10 ) as sys.odcinumberlist))
11 where substr(p_in,instr(' '||p_in,' ',1,column_value),1) != '(' );
12 return result;
13 end;
14 /
Function created.
SQL> select get_initials('Mechanical Engineering') from dual;
GET_INITIALS('MECHANICALENGINEERING')
----------------------------------------------------------------------------------------
ME
1 row selected.
SQL> select get_initials('Computer Science Engineering (New)') from dual;
GET_INITIALS('COMPUTERSCIENCEENGINEERING(NEW)')
----------------------------------------------------------------------------------------
CSE
1 row selected.
|
|
|
|
| Re: How to retrieve first letter of every word from string [message #597465 is a reply to message #597464] |
Fri, 04 October 2013 14:46 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> create or replace function get_initials (p_in varchar2) return varchar2
2 is
3 result varchar2(100);
4 initial varchar2(1);
5 begin
6 for i in 1..length(p_in)-length(replace(p_in,' ',''))+1 loop
7 initial := substr(p_in,instr(' '||p_in,' ',1,i),1);
8 if initial != '(' then
9 result := result || initial;
10 end if;
11 end loop;
12 return result;
13 end;
14 /
Function created.
SQL> select get_initials('Mechanical Engineering') from dual;
GET_INITIALS('MECHANICALENGINEERING')
------------------------------------------------------------------------------
ME
1 row selected.
SQL> select get_initials('Computer Science Engineering (New)') from dual;
GET_INITIALS('COMPUTERSCIENCEENGINEERING(NEW)')
------------------------------------------------------------------------------
CSE
1 row selected.
|
|
|
|
| Re: How to retrieve first letter of every word from string [message #597471 is a reply to message #597433] |
Fri, 04 October 2013 15:40 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Using REGULAR EXPRESSION -
SQL> WITH t
2 AS (SELECT 'Computer Science Engineering' str
3 FROM dual
4 UNION ALL
5 SELECT 'Mechanical Engineering'
6 FROM dual
7 UNION ALL
8 SELECT 'Oracle Frequently Asked Questions is OraFAQ'
9 FROM dual)
10 SELECT str,
11 Regexp_replace(str, '(^| )([^ ])|.', '\2') initial_letters
12 FROM t;
STR INITIAL_LETTERS
------------------------------------------- -------------------------------------------------------------------------------
Computer Science Engineering CSE
Mechanical Engineering ME
Oracle Frequently Asked Questions is OraFAQ OFAQiO
Regards,
Lalit
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 18:59:47 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
its working correctly the way I wanted. Thanks