Clever Geek Handbook

Oracle SQL - parse name string and convert it to start and last name

Does anyone know how to put this line: "Smith, John R"
into this line: "jsmith"?

I need to omit everything with lower ()
Find where the comma is and keep track of its integer location value
Get the first character after that comma and put it in front of the string
Then get the entire last name and insert it after the first initial one. Sidenote - instr () function is incompatible with my version

Thanks for any help!

+1


source to share


5 answers


Start by writing your own INSTR function - for example, call it my_instr. It will start at char 1 and loop until it finds a ','.



Then use like INSTR.

+2


source


The best way to do this is using the Oracle Regular Expressions feature, for example:

SELECT LOWER(regexp_replace('Smith, John R', 
             '(.+)(, )([A-Z])(.+)', 
             '\3\1', 1, 1)) 
  FROM DUAL;

This says: 1) when you find a pattern of any character set followed by a "," followed by an uppercase character, followed by any remaining characters, take the third element (the initial of the first name) and add the Last Name. Then make everything lowercase.



Your note: "The instr () function is incompatible with my version" doesn't make sense to me, since this function has been around for ages. Check your version as regular expressions were only added in Oracle in version 9i.

Thanks for the glasses.

- Quenching

+2


source


instr () is not compatible with your version of what? Oracle? Are you using version 4 or something?

+1


source


It's hard for me to believe that you don't have access to the correct instr (), but if so, do your own version.

Assuming you fixed it:

select 
  substr ( 
      lower ('Smith, John R')
    , instr ('Smith, John R', ',') + 2
    , 1 
  ) || - first_initial
  substr ( 
      lower ('Smith, John R')
    , 1
    , instr ('Smith, John R', ',') - 1 
  ) - last_name
from dual;

Also, be careful in your assumption that all names will be in this format. Keep track of something other than one space after the comma, surnames that have data like "Parisi, Jr." etc.

+1


source


There is no need to create your own function and to be honest it seems like a waste of time when it can be done quite easily with pre-existing sql functions. Care must be taken when using sloppy data entry.

Here's another way to achieve your goal:

with name_list as
  (select '   Parisi, Kenneth R' name from dual)
select name
      -- There may be a space after the comma.  This will strip an arbitrary
      -- amount of whitespace from the first name, so we can easily extract
      -- the first initial.
     , substr(trim(substr(name, instr(name, ',') + 1)), 1, 1) AS first_init
      -- a simple substring function, from the first character until the
      -- last character before the comma.
     , substr(trim(name), 1, instr(trim(name), ',') - 1) AS last_name
      -- put together what we have done above to create the output field      
     , lower(substr(trim(substr(name, instr(name, ',') + 1)), 1, 1)) ||
       lower(substr(trim(name), 1, instr(trim(name), ',') - 1)) AS init_plus_last
  from name_list;

NTN, Gabe

+1


source






More articles:


All Articles