SQL to Seperating Name Suffix from Last Name

Here is a cool piece of SQL that will separate the Name Suffix from the Last Name.  Since name suffix is a legal part of the name we collect it.  But our recruiting software vendor does not collect name suffix in a separate field and recommends that we pass it to PeopleSoft in one field, the last name field.  Since, by law the name on your w2 is suppose to match the name on your ssn card, we really want to collect this data and store it in its appropriate field.

This is where the SQL below comes in, this sql takes one input, Last Name, and returns two outputs.  Last Name and Name Suffix.    For Instance if you pass it the Last Name Weaver jr., it will return Weaver as the last name and Jr. as the Name Suffix.   If you pass it St. John, it would return St. John as the last name and blank as the Name Suffix.


Select Case Substr(Upper(:1) ,(Instr(:1 ,' ') + 1) ,(Length( :1)- Instr( :1 ,' '))) When 'JR' Then Substr(:1 , 1 ,(Instr( :1 ,' ') - 1)) When 'III' Then Substr(:1 , 1 ,(Instr( :1,' ') - 1)) When 'SR' Then Substr(:1 , 1 ,(Instr( :1 ,' ') - 1)) When 'II' Then Substr(:1 , 1 ,(Instr( :1 ,' ') - 1)) When 'IV' Then Substr(:1 , 1 ,(Instr( :1 ,' ') - 1)) Else :1 End ,  Case Substr( Upper(:1) ,(Instr( :1 ,' ') + 1) ,(Length( :1)- Instr( :1 ,' '))) When 'JR' Then 'Jr.' When 'III' Then 'III' When 'SR' Then 'Sr.' When 'II' Then 'II' When 'IV' Then 'IV' Else ' ' End
    FROM DUAL

The only problem with this SQL is it starts from the position 1 and finds the first white space, and with name suffix we need to find the last white space. So if you pass it St. John Jr. it would return St. John Jr. in the last name and blank in the name suffix.  So one small change will allow the INSTR function to start from the end of the string and search backwards for the last white space so this will work correctly.


 Select Case Substr(Upper(:1) ,(Instr(:1 ,' ', - 1) + 1) ,(Length( :1)- Instr( :1 ,' ', -1))) When 'JR' Then Substr(:1 , 1 ,(Instr( :1 ,' ', -1) - 1)) When 'III' Then Substr(:1 , 1 ,(Instr( :1,' ') - 1)) When 'SR' Then Substr(:1 , 1 ,(Instr( :1 ,' ', -1) - 1)) When 'II' Then Substr(:1 , 1 ,(Instr( :1 ,' ', -1) - 1)) When 'IV' Then Substr(:1 , 1 ,(Instr( :1 ,' ', -1) - 1)) Else :1 End ,  Case Substr( Upper(:1) ,(Instr( :1 ,' ', -1) + 1) ,(Length( :1)- Instr( :1 ,' ', -1))) When 'JR' Then 'Jr.' When 'III' Then 'III' When 'SR' Then 'Sr.' When 'II' Then 'II' When 'IV' Then 'IV' Else ' ' End
    FROM DUAL