Separating a Column String Data into many columns SQL [duplicate]

Question

How to separate a column string value into multiple columns. Say I have a row with the name of "John Bob UnderWood JR" and another name of "Sally Woods Feld" Could anyone help me out with an example. I'm having issues doing substrings and CHARINDEX with names longer than two values. Below I have it working for a two name field, but not sure how to continue with more than two names.

SELECT substring(Username, 1, CHARINDEX(' ',Username)-1) FirstName, 
substring(Username, CHARINDEX(' ',Username)+1, LEN(Username)) LastName 
FROM tblName

I have also used:

Select parsename(Replace(name_ind, ' ', '.'), 3) as LastName,
parsename(Replace(name_ind, ' ', '.'), 2) as FirstName,
parsename(Replace(name_ind, ' ', '.'), 1) as MiddleName,
parsename(Replace(name_ind, ' ', '.'), 4) as Suffix
from UspfoWeb.dbo.tbl_pers_svcmbr_tbl_go;

Which comes close to the output I would like to see but when there is a suffix in a name it goes into the last name spot and if only has two names than their last name goes into the middle name column. I'm assuming I need to create an if statement to sort them correct?

Expected outcome:

firstName    lastname   middleName   Suffix
John         Bob        Underwood    Jr.
Sally        Woods      Feld

Sometimes people have a space in their first name, such as Anne Marie or Jean Luc. — NoAlias
– NoAlias, Commented Mar 27, 2019 at 19:12
There are dozens of "split string" functions available, including STRING_SPLIT in SQL2016 and up. — pmbAustin
– pmbAustin, Commented Mar 27, 2019 at 19:17

Dheerendra · Accepted Answer · 2019-03-28 16:11:30Z

0

You can use the below code on similar lines.

GO
CREATE FUNCTION dbo.SplitStrings
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS @Tab TABLE(ID INT IDENTITY,Item NVARCHAR(10))  
AS
BEGIN
      INSERT INTO @Tab(Item)   
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   RETURN;
END
GO

SELECT *
,
(SELECT Item
FROM dbo.SplitStrings(UserName,' ')
WHERE ID = 1) AS [First Name]
,
(SELECT Item
FROM dbo.SplitStrings(UserName,' ')
WHERE ID = 2) AS [Last Name]
,
(SELECT Item
FROM dbo.SplitStrings(UserName,' ')
WHERE ID = 3) AS [Middle Name]
,   
(SELECT Item
FROM dbo.SplitStrings(UserName,' ')
WHERE ID = 4) AS [Suffix]
FROM UserTable

edited Mar 28, 2019 at 16:11

answered Mar 27, 2019 at 19:21

Dheerendra

3081 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NeoAer Over a year ago

This answer doesn't describe how I would do this with multiple names unless i'm wrong. Looks like I have to declare a different string every time.

Dheerendra Over a year ago

Edited the answer to suit the multiple records scenario

Collectives™ on Stack Overflow

Separating a Column String Data into many columns SQL [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related