How to get the substring value from a column with data?

Hi Friends,
How to get the substring value from a column with data?

Example
-------

Names Initials
----- --------
MTOLISI
FERNANDO
MAKGATLISHA SWARTBOOI
SIVENGI
T A
SAMANTHA MARY

I need to populate the initials column by taking the first character from the Names field
My Initials column should be as below

Initials
--------
M
F
MS
S
T A
SM

Thanks Guys,
Kishan

select substr(names,1,1) initials from tablename

SELECT
substr(fullnames,1,1)||substr(fullnames,instr(fullnames,' ')+1,1) initials
FROM
names

select substr(names,1,1) || substr(names, instr(names,' ')+1,1) from tablename;

You might want to add a decode before the second concatenation to check if there is a space.

Decode not necessary. Who cares? ;)

translate(initcap(column),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
Will deal with any number of names - but I feel there must be a neater way . . .

DaPi, you so sneaky!
I might use this trick somewhere.
:D

Watch out for names like "Hervé Niño 13th" it'll need some adjusting!

Or maybe just use the upper to make everything uppercase.

Quote:

---

Originally posted by oracle_faq
Or maybe just use the upper to make everything uppercase.

---

How would that help? :confused:

Yeah _faq, pay attention to example above. What's up with that?
DaPi used initcap for specific purpose.

:rolleyes:

Sorry wrong thought process took me somewhere else only.

Quote:

---

Originally posted by DaPi
translate(initcap(column),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
Will deal with any number of names - but I feel there must be a neater way . . .

---

you are something..!!!

PHP Code:

---

 create table test_to_del
 (
 name varchar2(20)
 );

 insert into test_to_del values ('saM len');
  insert into test_to_del values ('a b');
   insert into test_to_del values ('q v');

select replace(translate(initcap(name),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')
from test_to_del;


REPLACE(TRANSLATE(IN
--------------------
SL
AB
QV

drop table test_to_del; 


---

Quote:

---

Originally posted by rshivagami
select substr(names,1,1) || substr(names, instr(names,' ')+1,1) from tablename;

You might want to add a decode before the second concatenation to check if there is a space.

---

Thanks shivagami,
but I am not able to get the correct output.can you pls show me how it works by using the decode.
For instance samantha Mary - it should display the initial as SM
but as per the above query it is displaying only S

Thanks a lot
Kishan

Quote:

---

Originally posted by aspdba
you are something..!!!

PHP Code:

---

 create table test_to_del
 (
 name varchar2(20)
 );

 insert into test_to_del values ('saM len');
  insert into test_to_del values ('a b');
   insert into test_to_del values ('q v');

select replace(translate(initcap(name),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')
from test_to_del;


REPLACE(TRANSLATE(IN
--------------------
SL
AB
QV

drop table test_to_del; 


---

---

Thanks DAPI. It's Excellent.

Kishan

Kishan, are you saying mine and shivagami's code did not work? (they are identical)
Are you sure you were not running some other code?
d