Lets Say we have a table "Employee" with following data
SQL> select employeename from employee;
Use following query to extract Initials of names.
SQL> select employeename, upper(regexp_replace(employeename,'(^| )([^ ])([^ ])*','\2')) Initials from Employee;
Related Links:
Extracting text between html tags (removing html tags)
http://nimishgarg.blogspot.com/2011/09/extracting-text-between-html-tags.html
Oracle: Extract Numbers from String (Ex: Pin from Address)
http://nimishgarg.blogspot.com/2010/04/oracle-sql-extract-numbers-from-string.html
SQL> select employeename from employee;
Use following query to extract Initials of names.
SQL> select employeename, upper(regexp_replace(employeename,'(^| )([^ ])([^ ])*','\2')) Initials from Employee;
Related Links:
Extracting text between html tags (removing html tags)
http://nimishgarg.blogspot.com/2011/09/extracting-text-between-html-tags.html
Oracle: Extract Numbers from String (Ex: Pin from Address)
http://nimishgarg.blogspot.com/2010/04/oracle-sql-extract-numbers-from-string.html
Nice Query. A real Help for me.
ReplyDeletehey
ReplyDeleteafter running ur query i get only last letters in rows as seen below
select ename,upper(regexp_replace(ename,'(^|)([^])([^])*','\2'))initials from emp
lot of questons arise as beause imn't sure on regular expressions ,like what is the expresion to get first intials in a row or how to get same letter 'l' in a record like 'los angels' from n records ineed to pull particular matching 'L' records can u pls help me ?....
you forgot to put spaces. check
ReplyDeleteselect ename,upper(regexp_replace(ename,'(^|)([^])([^])*','\2'))initials, upper(regexp_replace(ename,'(^| )([^ ])([^ ])*','\2')) from emp;
and for only first letter use
select ename, upper(substr(ename,1,1)) first_initial from emp;
thanks
ReplyDeletei dint get ur query...
ReplyDelete:(
please xplain.....
you need to study little about regular expressions, check out following link
Deletehttp://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm
Hi Can Anyone tell me whats happening here?
ReplyDeleteyou need to study little about regular expressions, check out following link for detail
Deletehttp://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm
what is this (^|) means here in this query
ReplyDeletestarting of staring or space
DeleteThank You very Much!
ReplyDeleteArticle looks great, thanks for sharing with us. Gmail Email Address Grabber
ReplyDelete