Split double character of middle initial using CASE expression in a SQL Server stored procedure

Question

For example: instead "Juan Dela Cruz Eugenio" to become "Juan DC. Eugenio"

The expected flow

My attempt:

SELECT
(
    UPPER(lastName) 
    + ', '
    +UPPER(SUBSTRING(firstName,1,1)) + LOWER(SUBSTRING(firstName,2,99))
    +' '
    +Suffix
    +' '
    +(
        case
            when tb_persons.middleName = '' or tb_persons.middleName = ' ' or tb_persons.middleName is null
            then ''
            else
            UPPER(SUBSTRING(tb_persons.middleName,1,1))+'.'
        end)
) as name,
UPPER(SUBSTRING(firstName,1,1)) + LOWER(SUBSTRING(firstName,2,99)) as 'firstName',
UPPER(lastName) as 'lastName',middleName

FROM tb_persons

What if the string is `'Juan Dela Cruz'` or `'Juan Dela Cruz Eugenio Whatever'`? — Ilyes, Oct 03 '19 at 10:28
Must read: [Falsehoods Programmers Believe About Names](https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/). And what about Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso? — Zohar Peled, Oct 03 '19 at 10:56
Hi Jhon, Please to not poste pictures... Especially in this case, where the picture is nothing else then text. Why not add this text to the question? If I get your "flow" correctly, my suggestion below get's you rigth there. Just the final concatenation of the name parts is to be done... Did you check this? — Shnugo, Oct 03 '19 at 15:27

score 2 · Answer 1 · answered Oct 03 '19 at 10:38

Since you didn't respond to my comment, and until that, there is two ways to solve this (according to what you provide)

Method 1:

SELECT CONCAT(
               PARSENAME(REPLACE(Name, ' ', '.'), 4),
               ' ',
               SUBSTRING(PARSENAME(REPLACE(Name, ' ', '.'), 3), 1, 1),
               SUBSTRING(PARSENAME(REPLACE(Name, ' ', '.'), 2), 1, 1),
               '.',
               PARSENAME(REPLACE(Name, ' ', '.'), 1) 
             )
FROM
(
  VALUES
  ('Juan Dela Cruz Eugenio')
) T(Name)

Method 2

SELECT STRING_AGG( CASE RN WHEN 2 THEN SUBSTRING(V, 1, 1)
                           WHEN 3 THEN SUBSTRING(V, 1, 1)
                           WHEN 4 THEN '.' + V
                   ELSE V + ' ' END, ''
                 )
FROM
(
  VALUES
  (1, 'Juan Dela Cruz Eugenio')
) T(Id, Name) CROSS APPLY
(
  SELECT Value, ROW_NUMBER() OVER(ORDER BY Id) RN
  FROM STRING_SPLIT(Name, ' ')
) TT(V, RN)
GROUP BY Id

Online Demo

The usage of `PARSENAME` in this use case is - uhm - brave ;-) And about your second approach: `STRING_SPLIT()` is not guaranteed to return the expected sort order. On a SQL Server 2016+ it is much [better, faster and secure in sorting to use json methods](https://stackoverflow.com/a/38274439/5089204) (read the both update sections). — Shnugo, Oct 03 '19 at 14:04

score 1 · Accepted Answer · answered Oct 03 '19 at 14:00

You were told already, that dealing with names can be very tricky...

You might try something along this:

--A mockup-sceanrio to simulate your issue

DECLARE @mockupTable TABLE(ID INT IDENTITY, YourNameString VARCHAR(1000));
INSERT INTO @mockupTable VALUES
 ('Juan Dela Cruz Eugenio')
,('Jhon Lester')
,('Jhon MiddleName Lester')
,('Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso');

--The query

SELECT castedAndSplit.value('/x[1]','nvarchar(100)') AS FirstName
      ,CASE castedAndSplit.value('count(/x)','int') WHEN 1 THEN ''
                                                    WHEN 2 THEN ''  
                                                    WHEN 3 THEN CONCAT(castedAndSplit.value('substring(/x[2],1,1)','nchar(1)')
                                                                      ,'.')
                                                    ELSE        CONCAT(castedAndSplit.value('substring(/x[2],1,1)','nchar(1)')
                                                                      ,castedAndSplit.value('substring(/x[3],1,1)','nchar(1)')
                                                                      ,'.') END AS MiddleName
      ,castedAndSplit.value('/x[last()]','nvarchar(100)') AS LastName
FROM @mockupTable t
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT t.YourNameString AS [*] FOR XML PATH('')),' ','</x><x>')+ '</x>' AS XML)) A(castedAndSplit);

The result

FirstName   MiddleName  LastName
Juan        DC.         Eugenio
Jhon                    Lester
Jhon        M.          Lester
Pablo       DJ.         Picasso

Hint: To concatenate the three in one is easy, I leave this up to you...

The idea in short:

With a trick using some string replacements we can convert the string to XML, where every fragment is taken separately.

Now we can pick the first and the last element and take them as FirstName and as LastName.

For the MiddleName we can use the count of fragments. 1 or 2 fragments won't have a middle name, 3 fragments will have one name in the middle and any counter higher than 3 will use the 2nd and the 3rd fragments initial.

score 0 · Answer 3 · answered Oct 03 '19 at 15:48

Just another option just for fun, if you don't mind a helper function.

Thanks Shnugo for the sample data and ridiculous name (which I edited) +1

FYI - The TVF can easily be migrated into the CROSS APPLY

Example

DECLARE @mockupTable TABLE(ID INT IDENTITY, YourNameString VARCHAR(1000));
INSERT INTO @mockupTable VALUES
 ('Juan Dela Cruz Eugenio')
,('Jhon Lester')
,('Jhon MiddleName Lester')
,('Pablo Diego José Francisco de Paula Juan Nepomuceno Picasso'); 


Select A.*
      ,FirstName  = Pos1
      ,MiddleInit = concat(nullif(
                    concat( left(Pos2+Pos3,1)
                           ,left(Pos3+Pos4,1)
                           ,left(Pos4+Pos5,1)
                           ,left(Pos6+Pos7,1)
                           ,left(Pos7+Pos8,1)
                           ,left(Pos8+Pos9,1)
                          )
                    ,'')+'.','')
      ,LastName   = coalesce(Pos9,Pos8,Pos7,Pos6,Pos5,Pos4,Pos3,Pos2)
 From  @mockupTable A
 Cross Apply [dbo].[tvf-Str-Parse-Row](YourNameString,' ') B

Returns

The Function if Interested

ALTER FUNCTION [dbo].[tvf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
          ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
          ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
          ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
          ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
          ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
          ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
    From  ( values (cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml))) as A(xDim)
)

Split double character of middle initial using CASE expression in a SQL Server stored procedure

3 Answers3