Help Parsing String

Question

Help Parsing String

Tav29

SSC Enthusiast

Points: 139
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: 139 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: 246695 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;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Tav29 SSC Enthusiast Points: 139 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.