How to split names into first/middle/last if there are people who typed last/first/middle? Order known

Question

I am trying to split names into first, middle, last, based on an indicated order. I am at a lost on how to do this, and any help would be much appreciated. I am using sql server 2008 for work.

I attached an example dataset and the ideal dataset I would like to create.

ID  ORDER                   NAME
1   first, middle, last     Bruce, Batman, Wayne
2   middle, last, first     Superman, Kent, Clark
3   last, first, middle     Prince, Diana, Wonderwoman

INTO:

ID  ORDER                   NAME
1   first                   Bruce
1   middle                  Batman
1   last                    Wayne
2   middle                  Superman
2   last                    Kent
2   first                   Clark
3   last                    Prince
3   first                   Diana
3   middle                  Wonderwoman

Why not make the effort to normalise your data, rather than making another form of denormalisation? — Thom A, Jun 15 '19 at 21:33
@larnu thinking wishfully, perhaps this question is because that normalisation is being undertaken ;) — Caius Jard, Jun 16 '19 at 06:42
Considering you could normalise the data in one go, I sitting on the side of "unlikely" for that one @CaiusJard :( — Thom A, Jun 16 '19 at 07:03

score 0 · Answer 1 · answered Jun 15 '19 at 21:11

SQL Server does not have very good string processing functions. You can do this using a recursive CTE, though:

with cte as (
      select id,
             convert(varchar(max), left(ord, charindex(',', ord) - 1)) as ord,
             convert(varchar(max), left(name, charindex(',', name) - 1)) as name,
             convert(varchar(max), stuff(ord, 1, charindex(',', ord) + 1, '')) as ord_rest,
             convert(varchar(max), stuff(name, 1, charindex(',', name) + 1, '')) as name_rest,
             1 as lev
      from t
      union all
      select id,
             convert(varchar(max), left(ord_rest, charindex(',', ord_rest + ',') - 1)) as ord,
             convert(varchar(max), left(name_rest, charindex(',', name_rest + ',') - 1)) as name,
             convert(varchar(max), stuff(ord_rest, 1, charindex(',', ord_rest + ',') + 1, '')) as ord_rest,
             convert(varchar(max), stuff(name_rest, 1, charindex(',', name_rest + ',') + 1, '')) as name_rest,
             lev + 1
      from cte
      where ord_rest <> '' and lev < 10
     )
select id, ord, name
from cte
order by id, lev

Here is a db<>fiddle.

score 0 · Answer 2 · answered Jun 15 '19 at 21:35

With the help of a parse/split function that returns the sequence, this becomes a small matter using a CROSS APPLY

Example

Select A.ID 
      ,B.*
 From  YourTable A
 Cross Apply (
                Select [Order] = B1.RetVal
                      ,[Name]  = B2.RetVal
                 From  [dbo].[tvf-Str-Parse]([ORDER],',') B1
                 Join  [dbo].[tvf-Str-Parse]([NAME] ,',') B2 on B1.RetSeq=B2.RetSeq
             ) B

Returns

ID  Order   Name
1   first   Bruce
1   middle  Batman
1   last    Wayne
2   middle  Superman
2   last    Kent
2   first   Clark
3   last    Prince
3   first   Diana
3   middle  Wonderwoman

The Function if Interested

CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = row_number() over (order by 1/0)
          ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  ( values (cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.'))) as A(x)
    Cross Apply x.nodes('x') AS B(i)
);

Thank you! It has solved most of my problems and helped me figure out what I need to do next. — Tina Chan, Jun 16 '19 at 19:08

Caius Jard · Answer 3 · 2019-06-16T06:37:11.110

I found the other answers a bit hard to follow - they're neat tricks for sure but I think anyone coming to maintain them might be like "whaaaat?". Here I work out the indexes of the commas (the first comma string index goes in o1/n1, the second comma goes in o2/n2) in the first cte, cut the string up (substring between 1 and first comma, substring between first and second comma, substring after third comma) in the second cte and then use a couple of unions to turn the results from 7 columns into 3

WITH idxs AS
(
SELECT 
  id,
  order,
  name,
  CHARINDEX(',', [order]) as o1,
  CHARINDEX(',', [order], CHARINDEX(',', [order]) + 1) as o2,
  CHARINDEX(',', name) as n1,
  CHARINDEX(',', name, CHARINDEX(',', name) + 1) as n2
FROM
  t
),
cuts as (
SELECT
  id,
  SUBSTRING([order], 1, o1-1) as ord1,
  SUBSTRING([order], o1+1, o2-o1-1) as ord2,
  SUBSTRING([order], o2+1, 4000) as ord3,
  SUBSTRING(name, 1, n1-1) as nam1,
  SUBSTRING(name, n1+1, n2-n1-1) as nam2,
  SUBSTRING(name, n2+1, 4000) as nam3
FROM
  idxs
)

SELECT id, ord1 as [order], nam1 as name FROM cuts
UNION ALL
SELECT id, ord2, nam2 FROM cuts
UNION ALL
SELECT id, ord3, nam3 FROM cuts

Note that if your data sometimes has spaces in and sometimes does not you'll benefit from using either LTRIM/RTRIM in the output

if the spaces are always there after a comma, you could also adjust the substring indexes to cut the spaces out (any start index that is x+1 would be x+2 and the length would hence have to be -2)

How to split names into first/middle/last if there are people who typed last/first/middle? Order known

3 Answers3