Help Parsing String

Question

Help Parsing String

Tav29

SSC Enthusiast

Points: 144
More actions
December 3, 2022 at 11:04 pm

#4121614

I can do the Last Name, First Name; my problem is Middle Initial.
I have a field called EmpName, and the Values are:
EmpName(There is no space after the Comma)
1.       Smith,John
2.       Smith,John J
3.       Smith,John Michael
4.       Smith Creola,Melisa Lue

Results:
LastName         FirstName                        Middle Initial
1.       Smith                  John
2.       Smith                  John                                          J
3.       Smith                  John Michael
4.       Smith Creola      Melissa Lue

And also would like for record #4 to grab “Smith Creo” Only .( I would like to get the complete last Name first part “Smith” and only the first 4 characters of the second part of the last name “Creola”.

Thank you for the help in advanced.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Tav29 SSC Enthusiast Points: 144 More actions · Answer 1

Correction I need help with also the first name because I'm getting the middle Initial in my first name results;Example First Name Results= John J

Phil Parkin SSC Guru Points: 246944 More actions · Answer 2

I suspect you'll find that this is a fairly thankless task, once you expand your dataset to include other name varieties, but this code works for your sample data. It depends on the existence of Jeff Moden's infamous delimitedSplit8K function.

DROP TABLE IF EXISTS #SomeData;

CREATE TABLE #SomeData
(
    SomeText1 VARCHAR(200) NOT NULL
);

INSERT #SomeData
(
    SomeText1
)
VALUES
('Smith,John')
,('Smith,John J')
,('Smith,John Michael')
,('Smith Creola,Melisa Lue');

WITH Breakdown1
AS (SELECT *
    FROM #SomeData sd
        CROSS APPLY
    (
        SELECT CommaBreakNo = dsk.ItemNumber
              ,CommaItem = dsk.Item
        FROM dbo.DelimitedSplit8K (sd.SomeText1, ',') dsk
    ) c1
        CROSS APPLY
    (
        SELECT SpaceBreakNo = dsk.ItemNumber
              ,SpaceItem = dsk.Item
        FROM dbo.DelimitedSplit8K (c1.CommaItem, ' ') dsk
    ) c2 )
    ,Breakdown2
AS (SELECT TheRest1 = MAX (IIF(bd.CommaBreakNo = 2 AND bd.SpaceBreakNo = 1, bd.SpaceItem, ''))
          ,TheRest2 = MAX (IIF(bd.CommaBreakNo = 2 AND bd.SpaceBreakNo = 2, bd.SpaceItem, ''))
          ,LastName1 = MAX (IIF(bd.CommaBreakNo = 1 AND bd.SpaceBreakNo = 1, bd.SpaceItem, ''))
          ,LastName2 = MAX (IIF(bd.CommaBreakNo = 1 AND bd.SpaceBreakNo = 2, bd.SpaceItem, ''))
    FROM Breakdown1 bd
    GROUP BY bd.SomeText1)
SELECT LastName = bd2.LastName1 + ' ' + IIF(LEN (bd2.LastName2) > 4, LEFT(bd2.LastName2, 4), bd2.LastName2)
      ,FirstName = bd2.TheRest1 + IIF(c3.IsMiddle = 1, '', CONCAT (' ', bd2.TheRest2))
      ,MiddleInitial = IIF(c3.IsMiddle = 1, bd2.TheRest2, '')
FROM Breakdown2 bd2
    CROSS APPLY
(SELECT IsMiddle = IIF(LEN (bd2.TheRest2) = 1, 1, 0)) c3;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tav29 SSC Enthusiast Points: 144 More actions · Answer 3

Yes, I searched and came across "Jeff Moden's infamous delimitedSplit8K function". I was looking for an alternative way. I will implement Jeff Moden's infamous delimitedSplit8K function. Thanks for the reply.