digstarian

SQL salutation, forename, surname splitter

Jun 26th, 2013
518
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 4.61 KB | None | 0 0
  1. SELECT
  2.   FIRST_NAME.ORIGINAL_INPUT_DATA
  3.  ,FIRST_NAME.TITLE
  4.  ,FIRST_NAME.FIRST_NAME
  5.  ,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
  6.        THEN NULL  --no more spaces?  assume rest is the last name
  7.        ELSE SUBSTRING(
  8.                        FIRST_NAME.REST_OF_NAME
  9.                       ,1
  10.                       ,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
  11.                      )
  12.        END AS MIDDLE_NAME
  13.  ,SUBSTRING(
  14.              FIRST_NAME.REST_OF_NAME
  15.             ,1 + CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
  16.             ,LEN(FIRST_NAME.REST_OF_NAME)
  17.            ) AS LAST_NAME
  18. FROM
  19.   (  
  20.   SELECT
  21.     TITLE.TITLE
  22.    ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
  23.          THEN TITLE.REST_OF_NAME --No space? return the whole thing
  24.          ELSE SUBSTRING(
  25.                          TITLE.REST_OF_NAME
  26.                         ,1
  27.                         ,CHARINDEX(' ',TITLE.REST_OF_NAME)-1
  28.                        )
  29.     END AS FIRST_NAME
  30.    ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)  
  31.          THEN NULL  --no spaces @ all?  then 1st name is all we have
  32.          ELSE SUBSTRING(
  33.                          TITLE.REST_OF_NAME
  34.                         ,CHARINDEX(' ',TITLE.REST_OF_NAME)+1
  35.                         ,LEN(TITLE.REST_OF_NAME)
  36.                        )
  37.     END AS REST_OF_NAME
  38.    ,TITLE.ORIGINAL_INPUT_DATA
  39.   FROM
  40.     (  
  41.     SELECT
  42.       --if the first three characters are in this list,
  43.       --then pull it as a "title".  otherwise return NULL for title.
  44.       CASE /*WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
  45.            THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))*/
  46.                 WHEN (SUBSTRING(TEST_DATA.FULL_NAME,3,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,2) IN ('MR','MS','DR','FR'))
  47.                     THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,2)))
  48.                 WHEN (SUBSTRING(TEST_DATA.FULL_NAME,4,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MRS','LRD','SIR'))
  49.                     THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))
  50.                 WHEN (SUBSTRING(TEST_DATA.FULL_NAME,5,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,4) IN ('LORD','LADY','MISS','PROF'))
  51.                     THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,4)))
  52.            ELSE NULL
  53.            END AS TITLE
  54.       --if you change the list, don't forget to change it here, too.
  55.       --so much for the DRY prinicple...
  56.      ,CASE/* WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
  57.            THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))*/
  58.                 WHEN (SUBSTRING(TEST_DATA.FULL_NAME,3,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,2) IN ('MR','MS','DR','FR'))
  59.                     THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))
  60.                 WHEN (SUBSTRING(TEST_DATA.FULL_NAME,4,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MRS','LRD','SIR'))
  61.                     THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,5,LEN(TEST_DATA.FULL_NAME))))
  62.                 WHEN (SUBSTRING(TEST_DATA.FULL_NAME,5,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,4) IN ('LORD','LADY','MISS','PROF'))
  63.                     THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,6,LEN(TEST_DATA.FULL_NAME))))
  64.            ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
  65.            END AS REST_OF_NAME
  66.      ,TEST_DATA.ORIGINAL_INPUT_DATA
  67.     FROM
  68.       (
  69.       SELECT
  70.         --trim leading & trailing spaces before trying to process
  71.         --disallow extra spaces *within* the name
  72.         REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),'  ',' '),'  ',' ') AS FULL_NAME
  73.        ,FULL_NAME AS ORIGINAL_INPUT_DATA
  74.       FROM
  75.         (
  76.         --if you use this, then replace the following
  77.         --block with your actual table
  78.               SELECT 'GEORGE W BUSH' AS FULL_NAME
  79.         UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
  80.         UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
  81.         UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
  82.         UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
  83.         UNION SELECT 'TOMMY' AS FULL_NAME
  84.         UNION SELECT 'BILLY' AS FULL_NAME
  85.         UNION SELECT NULL AS FULL_NAME
  86.         UNION SELECT ' ' AS FULL_NAME
  87.         UNION SELECT '    JOHN  JACOB     SMITH' AS FULL_NAME
  88.         UNION SELECT ' DR  SANJAY       GUPTA' AS FULL_NAME
  89.         UNION SELECT ' DR.  SANJAY       GUPTA' AS FULL_NAME
  90.         UNION SELECT 'DR JOHN S HOPKINS' AS FULL_NAME
  91.         UNION SELECT ' MRS  SUSAN ADAMS' AS FULL_NAME
  92.         UNION SELECT ' MS AUGUSTA  ADA   KING ' AS FULL_NAME      
  93.         UNION SELECT ' MS. AUGUSTA  ADA   KING ' AS FULL_NAME      
  94.         UNION SELECT ' LADY AUGUSTA  ADA   KING ' AS FULL_NAME      
  95.         ) RAW_DATA
  96.       ) TEST_DATA
  97.     ) TITLE
  98.   ) FIRST_NAME
Add Comment
Please, Sign In to add comment