StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is a self-contained example, with easily manipulated test data. </p> <p>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".</p> <p>If the middle name is missing, then you just get FIRST_NAME and LAST_NAME (MIDDLE_NAME will be NULL).</p> <p>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.</p> <p><strong>Edit-- Handle the following special cases:</strong></p> <p><strong>1 - The NAME field is NULL</strong></p> <p><strong>2 - The NAME field contains leading / trailing spaces</strong></p> <p><strong>3 - The NAME field has > 1 consecutive space within the name</strong></p> <p><strong>4 - The NAME field contains ONLY the first name</strong></p> <p><strong>5 - Include the original full name in the final output as a separate column, for readability</strong></p> <p><strong>6 - Handle a specific list of prefixes as a separate "title" column</strong></p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload