How to Get Full Name of an Employee by Joining First, Middle and Last Name Considering Null in Java

SQL: parse the first, middle and last name from a fullname field

Here is a self-contained example, with easily manipulated test data.

With this example, if you have a name with more than three parts, then all the "extra" stuff will get put in the LAST_NAME field. An exception is made for specific strings that are identified as "titles", such as "DR", "MRS", and "MR".

If the middle name is missing, then you just get FIRST_NAME and LAST_NAME (MIDDLE_NAME will be NULL).

You could smash it into a giant nested blob of SUBSTRINGs, but readability is hard enough as it is when you do this in SQL.

Edit-- Handle the following special cases:

1 - The NAME field is NULL

2 - The NAME field contains leading / trailing spaces

3 - The NAME field has > 1 consecutive space within the name

4 - The NAME field contains ONLY the first name

5 - Include the original full name in the final output as a separate column, for readability

6 - Handle a specific list of prefixes as a separate "title" column

SELECT
  FIRST_NAME.ORIGINAL_INPUT_DATA
 ,FIRST_NAME.TITLE
 ,FIRST_NAME.FIRST_NAME
 ,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
       THEN NULL  --no more spaces?  assume rest is the last name
       ELSE SUBSTRING(
                       FIRST_NAME.REST_OF_NAME
                      ,1
                      ,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
                     )
       END AS MIDDLE_NAME
 ,SUBSTRING(
             FIRST_NAME.REST_OF_NAME
            ,1 + CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
            ,LEN(FIRST_NAME.REST_OF_NAME)
           ) AS LAST_NAME
FROM
  (  
  SELECT
    TITLE.TITLE
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
         THEN TITLE.REST_OF_NAME --No space? return the whole thing
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,1
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)-1
                       )
    END AS FIRST_NAME
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)  
         THEN NULL  --no spaces @ all?  then 1st name is all we have
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)+1
                        ,LEN(TITLE.REST_OF_NAME)
                       )
    END AS REST_OF_NAME
   ,TITLE.ORIGINAL_INPUT_DATA
  FROM
    (   
    SELECT
      --if the first three characters are in this list,
      --then pull it as a "title".  otherwise return NULL for title.
      CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))
           ELSE NULL
           END AS TITLE
      --if you change the list, don't forget to change it here, too.
      --so much for the DRY prinicple...
     ,CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))
           ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
           END AS REST_OF_NAME
     ,TEST_DATA.ORIGINAL_INPUT_DATA
    FROM
      (
      SELECT
        --trim leading & trailing spaces before trying to process
        --disallow extra spaces *within* the name
        REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),'  ',' '),'  ',' ') AS FULL_NAME
       ,FULL_NAME AS ORIGINAL_INPUT_DATA
      FROM
        (
        --if you use this, then replace the following
        --block with your actual table
              SELECT 'GEORGE W BUSH' AS FULL_NAME
        UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
        UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
        UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
        UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
        UNION SELECT 'TOMMY' AS FULL_NAME
        UNION SELECT 'BILLY' AS FULL_NAME
        UNION SELECT NULL AS FULL_NAME
        UNION SELECT ' ' AS FULL_NAME
        UNION SELECT '    JOHN  JACOB     SMITH' AS FULL_NAME
        UNION SELECT ' DR  SANJAY       GUPTA' AS FULL_NAME
        UNION SELECT 'DR JOHN S HOPKINS' AS FULL_NAME
        UNION SELECT ' MRS  SUSAN ADAMS' AS FULL_NAME
        UNION SELECT ' MS AUGUSTA  ADA   KING ' AS FULL_NAME      
        ) RAW_DATA
      ) TEST_DATA
    ) TITLE
  ) FIRST_NAME

How To Split full Name into First Name, Middle Name, Last Name and suffix in TSQL

SELECT 

d.First_Name


,CASE WHEN 0 = CHARINDEX(' ',d.REST_OF_NAME)
       THEN NULL  
       ELSE SUBSTRING(                                                    ---- finds the middle name from rest of the name 
                       d.REST_OF_NAME
                      ,1
                      ,CHARINDEX(' ',d.REST_OF_NAME)-1
                     )
       END AS Middle_Name

,SUBSTRING(
             d.REST_OF_NAME                                             ---- finds the Last name from rest of the name 
            ,1 + CHARINDEX(' ', d.REST_OF_NAME)
            ,LEN( d.REST_OF_NAME)
           ) AS Last_Name

,d.Suffix
,d.CUSTOMER_NUMBER
,D.Orignal_Data_String
from
(SELECT c.Suffix,

CASE WHEN 0 = CHARINDEX(' ',c.Remainding_Name_Part)
         THEN c.Remainding_Name_Part 
         ELSE SUBSTRING(                                                    ---- substring first name fro rest of the name from reminding part of the name 
                         c.Remainding_Name_Part
                        ,1
                        ,CHARINDEX(' ',c.Remainding_Name_Part)-1
                       )
    END AS First_Name
,CASE WHEN 0 = CHARINDEX(' ',c.Remainding_Name_Part)  
         THEN NULL  
         ELSE SUBSTRING(
                         c.Remainding_Name_Part
                        ,CHARINDEX(' ',c.Remainding_Name_Part)+1           ------    substring rest of the name after substracting firstname from the remainding partof the name
                        ,LEN(c.Remainding_Name_Part)
                       )
    END AS REST_OF_NAME

,c.CUSTOMER_NUMBER
,C.Orignal_Data_String
FROM 
(SELECT 
CASE WHEN RIGHT(b.Name,2) IN ('IV','Jr','Sr')
     THEN LTRIM(RTRIM(RIGHT(b.Name,2)))                                     ----finds suffix in name        
        WHEN RIGHT(b.Name,3) IN ('III','Esq',' II')
       THEN LTRIM(RTRIM(RIGHT(b.Name,3))) 

ELSE NULL
END AS [Suffix]
,
CASE WHEN RIGHT(b.Name,2) IN ('IV','Jr','Sr')
     THEN LTRIM(RTRIM(LEFT(b.name,LEN(b.name)-2)))                         ----finds remider part of name after subtrecting suffix        
       WHEN RIGHT(b.Name,3) IN ('III',' Esq',' II')
       THEN LTRIM(RTRIM(LEFT(b.name,LEN(b.name)-3)))

ELSE LTRIM(RTRIM(b.name))
END AS [Remainding_Name_Part]
,B.CUSTOMER_NUMBER
,B.Orignal_Data_String


FROM 

(SELECT 
REPLACE(REPLACE(LTRIM(RTRIM(a.NAME)),'  ',' '),'  ',' ') AS [Name]         ------ Clears spaces 
,A.NAME AS [Orignal_Data_String]
,a.CUSTOMER_NUMBER
FROM 
(
SELECT NAME,CUSTOMER_NUMBER                                                 ------ finds the customers
FROM [FIS_CORE_FEEDS_DM].[dbo].[FIS_DAILY_CUST_TABLE]
WHERE CUSTOMER_TYPE !='O'
        )A
     )B
   )C
)D

How to separate full name string into firstname and lastname string?

This will work if you are sure you have a first name and a last name.

string fullName = "Adrian Rules";
var names = fullName.Split(' ');
string firstName = names[0];
string lastName = names[1];

Make sure you check for the length of names.

names.Length == 0 //will not happen, even for empty string
names.Length == 1 //only first name provided (or blank)
names.Length == 2 //first and last names provided
names.Length > 2 //first item is the first name. last item is the last name. Everything else are middle names

Update

Of course, this is a rather simplified view on the problem. The objective of my answer is to explain how string.Split() works. However, you must keep in mind that some last names are composite names, like "Luis da Silva", as noted by @AlbertEin.

This is far from being a simple problem to solve. Some prepositions (in french, spanish, portuguese, etc.) are part of the last name. That's why @John Saunders asked "what language?". John also noted that prefixes (Mr., Mrs.) and suffixes (Jr., III, M.D.) might get in the way.

How to map Employee's column - manager as Employee instance

I suppose the MANAGER column is a foreign key on another employee, right?

If so, I would select all of the employee's without a manager first and add each one to a Map<Integer, Employee>. Then, when you iterate over the employee's with a manager, you can fetch the corresponding manager from this map and add it to the newly created employee instance.

This becomes more tricky if you have a hierarchy with more than 2 levels. If you can't order them by a property (e.g. department), then you have to read all of the employee's first and add their manager in a second step.

If you then need a set of employee's as a result, you'll only need to create a new Set of the maps' values.

How to sort a List of Persons by first name, last name and so on?

Concatenate all parts of the name and sort using them them. Implement a Comparable interface in a Person class.

public class Person implements Comparable<Person>{
...
    int compareTo(Person o){
         return (fName + lname).compareTo(o.getfName() + o.getLname());
    }
...
}

Then you can pass list of such objects to Collections.sort. If you have access to Person's class code it's better to use Comparable interface. Distinct comparator is useful when you are working with 3rd party libraries and you can't add implementation of a new interface.

How to pass Null (a real surname!) to a SOAP web service in ActionScript 3

Tracking it down

At first I thought this was a coercion bug where null was getting coerced to "null" and a test of "null" == null was passing. It's not. I was close, but so very, very wrong. Sorry about that!

I've since done lots of fiddling on wonderfl.net and tracing through the code in mx.rpc.xml.*. At line 1795 of XMLEncoder (in the 3.5 source), in setValue, all of the XMLEncoding boils down to

currentChild.appendChild(xmlSpecialCharsFilter(Object(value)));

which is essentially the same as:

currentChild.appendChild("null");

This code, according to my original fiddle, returns an empty XML element. But why?

Cause

According to commenter Justin Mclean on bug report FLEX-33664, the following is the culprit (see last two tests in my fiddle which verify this):

var thisIsNotNull:XML = <root>null</root>;
if(thisIsNotNull == null){
    // always branches here, as (thisIsNotNull == null) strangely returns true
    // despite the fact that thisIsNotNull is a valid instance of type XML
}

When currentChild.appendChild is passed the string "null", it first converts it to a root XML element with text null, and then tests that element against the null literal. This is a weak equality test, so either the XML containing null is coerced to the null type, or the null type is coerced to a root xml element containing the string "null", and the test passes where it arguably should fail. One fix might be to always use strict equality tests when checking XML (or anything, really) for "nullness."

Solution

The only reasonable workaround I can think of, short of fixing this bug in every damn version of ActionScript, is to test fields for "null" and escape them as CDATA values.

CDATA values are the most appropriate way to mutate an entire text value that would otherwise cause encoding/decoding problems. Hex encoding, for instance, is meant for individual characters. CDATA values are preferred when you're escaping the entire text of an element. The biggest reason for this is that it maintains human readability.

---

Related Topics