Retrieve initials from a SQL Server Table

Question

I've been working on treating a sql table, and splitting the data. I've come to splitting some initials from the last name. The only problem is, the initials are spaced out. For example (data from my table)

• Hanse J S P > J S P are the initials
• Gerson B D V > B D V are the initials
• J D Timberland > J D are the initials

So basically, it's up to four initials, that can be either at the begin, middle, or end of the string. I'm at a loss as to how I should import these. into a seperate column where the result will be:

COL A | COL B
J S P | Jansen
B D V | Gerson
J D   | Timberland

Can anyone please point me in the right direction? I'm using SQL Server.

Answer 1

Here's a rather hamfisted way of doing it by abusing the Parsename function. The big caveat here is that Parsename is limited to 4 tokens so J S P Jansen will work but J S P C Jansen or John J S P Jansen will not.

With parsedname AS
(
  SELECT
      PARSENAME(replace(name, ' ', '.'), 1) name1,
      PARSENAME(replace(name, ' ', '.'), 2) name2,
      PARSENAME(replace(name, ' ', '.'), 3) name3,
      PARSENAME(replace(name, ' ', '.'), 4) name4
   FROM yourtable
)
SELECT 
   CASE WHEN LEN(name4) = 1 THEN name4 ELSE '' END +
      CASE WHEN LEN(name3) = 1 THEN name3 ELSE '' END +
      CASE WHEN LEN(name2) = 1 THEN name2 ELSE '' END +
      CASE WHEN LEN(name1) = 1 THEN name1 ELSE '' END as initials,
   CASE WHEN LEN(name1) > 1 THEN name1 
      WHEN LEN(name2) > 1 THEN name2
      WHEN LEN(name3) > 1 THEN name3
      WHEN LEN(name4) > 1 THEN name4
      END as surname
FROM parsedname

Here is a sqlfiddle of this in action

CREATE TABLE NAMES (name varchar(50));
INSERT INTO NAMES VALUES ('J S P Jansen');
INSERT INTO NAMES VALUES ('B D V Gerson');
INSERT INTO NAMES VALUES ('J D Timberland');

With parsedname AS
(
  SELECT
      PARSENAME(replace(name, ' ', '.'), 1) name1,
      PARSENAME(replace(name, ' ', '.'), 2) name2,
      PARSENAME(replace(name, ' ', '.'), 3) name3,
      PARSENAME(replace(name, ' ', '.'), 4) name4
   FROM names
)
SELECT 
   CASE WHEN LEN(name4) = 1 THEN name4 ELSE '' END +
      CASE WHEN LEN(name3) = 1 THEN name3 ELSE '' END +
      CASE WHEN LEN(name2) = 1 THEN name2 ELSE '' END +
      CASE WHEN LEN(name1) = 1 THEN name1 ELSE '' END as initials,
   CASE WHEN LEN(name1) > 1 THEN name1 
      WHEN LEN(name2) > 1 THEN name2
      WHEN LEN(name3) > 1 THEN name3
      WHEN LEN(name4) > 1 THEN name4
      END as surname
FROM parsedname

+----------+------------+
| initials |  surname   |
+----------+------------+
| JSP      | Jansen     |
| BDV      | Gerson     |
| JD       | Timberland |
+----------+------------+

If a space is needed in between those letters you can just flip around that CASE statement to something like:

TRIM(CASE WHEN LEN(name4) = 1 THEN name4 + ' ' ELSE '' END +
      CASE WHEN LEN(name3) = 1 THEN name3 + ' ' ELSE '' END +
      CASE WHEN LEN(name2) = 1 THEN name2 + ' ' ELSE '' END +
      CASE WHEN LEN(name1) = 1 THEN name1 + ' ' ELSE '' END) as initials

SQLFiddle with the spaces

+----------+------------+
| initials |  surname   |
+----------+------------+
| J S P    | Jansen     |
| B D V    | Gerson     |
| J D      | Timberland |
+----------+------------+

Answer 2

This one uses CHARINDEX and recursive CTE to extract space delimited substrings from name:

• Find the substring before the first space
• Feed the remaining substring to the same CTE

Once you have the substrings, it is only a matter of gluing them back:

WITH yourdata(FullName) AS (
    SELECT 'Hanse J S P' UNION
    SELECT 'Gerson B D V' UNION
    SELECT 'J D Timberland' UNION
    SELECT 'TEST 1 TEST 2 TEST 3'
), cte AS (
    SELECT
        FullName,
        CASE WHEN Pos1 = 0 THEN FullName ELSE SUBSTRING(FullName, 1, Pos1 - 1) END AS LeftPart,
        CASE WHEN Pos1 = 0 THEN Null     ELSE SUBSTRING(FullName, Pos1 + 1, Pos2 - Pos1) END AS NextPart,
        1 AS PartSort
    FROM yourdata
    CROSS APPLY (SELECT CHARINDEX(' ', FullName) AS Pos1, LEN(FullName) AS Pos2) AS CA
    UNION ALL
    SELECT
        FullName,
        CASE WHEN Pos1 = 0 THEN NextPart ELSE SUBSTRING(NextPart, 1, Pos1 - 1) END,
        CASE WHEN Pos1 = 0 THEN Null     ELSE SUBSTRING(NextPart, Pos1 + 1, Pos2 - Pos1) END,
        PartSort + 1
    FROM cte
    CROSS APPLY (SELECT CHARINDEX(' ', NextPart) AS Pos1, LEN(NextPart) AS Pos2) AS CA
    WHERE NextPart IS NOT NULL
)
SELECT yourdata.FullName, STUFF(CA1.XMLStr, 1, 1, '') AS Initials, STUFF(CA2.XMLStr, 1, 1, '') AS Names
FROM yourdata
CROSS APPLY (
    SELECT CONCAT(' ', LeftPart)
    FROM cte
    WHERE FullName = yourdata.FullName AND LEN(LeftPart) = 1
    ORDER BY PartSort
    FOR XML PATH('')
) AS CA1(XMLStr)
CROSS APPLY (
    SELECT CONCAT(' ', LeftPart)
    FROM cte
    WHERE FullName = yourdata.FullName AND LEN(LeftPart) > 1
    ORDER BY PartSort
    FOR XML PATH('')
) AS CA2(XMLStr)

Result:

| FullName             | Initials | Names          |
|----------------------|----------|----------------|
| Gerson@B@D@V         | B D V    | Gerson         |
| Hanse@J@S@P          | J S P    | Hanse          |
| J@D@Timberland       | J D      | Timberland     |
| TEST@1@TEST@2@TEST@3 | 1 2 3    | TEST TEST TEST |