Oracle: Extract Initials of Names

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


12 comments:

  1. Nice Query. A real Help for me.

    ReplyDelete
  2. hey

    after 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 ?....

    ReplyDelete
  3. you forgot to put spaces. check
    select 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;

    ReplyDelete
  4. i dint get ur query...
    :(
    please xplain.....

    ReplyDelete
    Replies
    1. you need to study little about regular expressions, check out following link
      http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

      Delete
  5. Hi Can Anyone tell me whats happening here?

    ReplyDelete
    Replies
    1. you need to study little about regular expressions, check out following link for detail
      http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

      Delete