Oracle SQL - Parsing a name string and converting

Question

Does anyone know how to turn this string: "Smith, John R"
Into this string: "jsmith" ?

I need to lowercase everything with lower()
Find where the comma is and track it's 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 stick it after the first initial.

Sidenote - instr() function is not compatible with my version

Thanks for any help!

Answer 1

The best way to do this is using Oracle Regular Expressions feature, like this:

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

That says, 1) when you find the pattern of any set of characters, followed by ", ", followed by an uppercase character, followed by any remaining characters, take the third element (initial of first name) and append the last name. Then make everything lowercase.

Your side note: "instr() function is not compatible with my version" doesn't make sense to me, as that function's been around for ages. Check your version, because Regular Expressions was only added to Oracle in version 9i.

Thanks for the points.

-- Stew

Answer 2

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

Then use as you would INSTR.

Answer 3

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

Answer 4

There is no need to create your own function, and quite frankly, it seems a waste of time when this can be done fairly easily with sql functions that already exist. Care must be taken to account for sloppy data entry.

Here is another way to accomplish your stated 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;  

HTH, Gabe

Answer 5

I have a hard time believing you don’t have access to a proper instr() but if that’s the case, implement your own version.

Assuming you have that straightened out:

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 about your assumption that all names will be in that format. Watch out for something other than a single space after the comma, last names having data like “Parisi, Jr.”, etc.