How would you use string functions to separate data by individual terms

Question

I am trying to split names into columns, for example, column 1 named "Name would contain in row 1 the varChar "Jesus Lopez" How could I split it up so that I can create a second column with row 1 to contain "Jesus" and a third column with row 1 to contain "Lopez". I can only use string functions to accomplish this task.

I thought about using the Left() function and Nest Charindex() to find the first set of string. I'm trying to figure out gather the rest of the name and put it on its own column.

Select Name, 
     Left(Name, Charindex(' ', Name)) as FirstName,

From Person.StateProvince

I expect to have a total of 3 columns. One with the original name, another with the first name only, and lastly a third column with what ever is left from the data in the first column.

Possible duplicate of [Splitting a Full Name into First and Last Name](https://stackoverflow.com/questions/45959509/splitting-a-full-name-into-first-and-last-name) and [How do I split a string so I can access item x?](https://stackoverflow.com/q/2647/62576) — Ken White, Jun 18 '19 at 01:17

score 0 · Answer 1 · answered Jun 18 '19 at 03:40

0

What is the requirement with the spaces? Considering you want to ignore any leading or trailing spaces for the values, and making the value as NULL when the value is not there, this would work:

SELECT  Name
        ,FirstName = CASE WHEN CHARINDEX(' ', LTRIM(Name)) > 1 THEN LEFT(LTRIM(Name), CHARINDEX(' ', LTRIM(Name))-1) WHEN LEN(LTRIM(RTRIM(Name))) > 1 THEN LTRIM(RTRIM(Name)) ELSE NULL END
        ,Remaining = CASE WHEN CHARINDEX(' ', LTRIM(RTRIM(Name))) > 0 THEN LTRIM(SUBSTRING(LTRIM(Name),CHARINDEX(' ', LTRIM(Name))+1, LEN(LTRIM(Name)) - CHARINDEX(' ', LTRIM(Name)))) ELSE NULL END
FROM    Person.StateProvince;

answered Jun 18 '19 at 03:40

san

1,415
8
13

I'm still trying to figure out how this works, but it works! Thanks a million! – Jesus Lopez Jun 19 '19 at 00:04
Try to run individual component in separate SELECT statement and try to grasp the logic that way rather than looking at the whole thing together if you are finding it difficult to understand the full logic. – san Jun 19 '19 at 01:40

score 0 · Answer 2 · answered Jun 18 '19 at 03:50

If you're string has not more than four parts then you could use PARSENAME

SELECT [Name]
    ,PARSENAME(REPLACE([Name],' ','.'),4) AS Part1
    ,PARSENAME(REPLACE([Name],' ','.'),3) AS Part2
    ,PARSENAME(REPLACE([Name],' ','.'),2) AS Part3
    ,PARSENAME(REPLACE([Name],' ','.'),1) AS Part4
FROM #temp

How would you use string functions to separate data by individual terms

2 Answers2