CODEWITHSUNDEEP
sql-server
David Brierton
David Brierton

Reputation: 7397

MSSQL Parsing Name into two new columns and adding to the same table

I am using sql server and I am trying to figure out how to select the vp_timesheetpunch.personfullname [Assoc Name] which is a view and then parse it because the results show up "last name, first name Middle Name". I am trying to add three more columns after the vp_timesheetpunch.personfullname [Assoc Name] to show First Name in one column then last name and somehow throw in a middle name column (incase there is a middle name) in another right after the Assoc Name.

     select
            vp_timesheetpunch.personnum [Assoc ID],
            vp_timesheetpunch.personfullname [Assoc Name],
            vp_timesheetpunch.laborlevelname1 [Department],
            vp_timesheetpunch.laborlevelname4 [Salary Code],
            vp_timesheetpunch.eventdate [Shift Date],
            shiftassignmnt.shiftstartdate [Scheduled Start],
            vp_timesheetpunch.startdtm [Rounded Start],
            vp_timesheetpunch.inpunchdtm [Actual Start],
            vp_timesheetpunch.enddtm [Rounded End],
            vp_timesheetpunch.outpunchdtm [Actual End],
            vp_timesheetpunch.TIMEINSECONDS [Time in seconds],
            convert(decimal(7,2),vp_timesheetpunch.TIMEINSECONDS/3600.0) [Time in hours]
        from
            vp_timesheetpunch
        left outer join
            vp_punchexceptions
        on
            vp_timesheetpunch.timesheetitemid = vp_punchexceptions.timesheetitemid
        inner join
            timesheetitem
        on
            vp_timesheetpunch.timesheetitemid = timesheetitem.timesheetitemid
        inner join
            workedshift
        on
            timesheetitem.workedshiftid = workedshift.workedshiftid
        inner join
            shfasgnwshfmm
        on
            workedshift.workedshiftid = shfasgnwshfmm.workedshiftid
        inner join
            shiftassignmnt
        on
            shfasgnwshfmm.shiftassignid = shiftassignmnt.shiftassignid
        where
            --limit rows to the specified pay period
            vp_timesheetpunch.eventdate = '1/22/2019'
            --exclude rows that are missing data
            and vp_timesheetpunch.inpunchdtm is not null
            and vp_timesheetpunch.outpunchdtm is not null
            --limit rows to shifts with exceptions
        order by
            vp_timesheetpunch.personnum,
            vp_timesheetpunch.eventdate

Edit: I tried using case to parse but was unsuccessful with my attempts.

Any help with this would be greatly appreciated.

Upvotes: 1

Views: 41

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

Parsing names can be kind of ugly. Assuming you really have the two patterns something like this should work.

declare @FullName varchar(50) = 'Brierton, David H'

select LastName = case when parsename(replace(replace(@FullName, ',', ''), ' ', '.'), 3) is null
        then parsename(replace(replace(@FullName, ',', ''), ' ', '.'), 2)
        else parsename(replace(replace(@FullName, ',', ''), ' ', '.'), 3)
    end
    , FirstName = case when parsename(replace(replace(@FullName, ',', ''), ' ', '.'), 3) is null
        then parsename(replace(replace(@FullName, ',', ''), ' ', '.'), 1)
        else parsename(replace(replace(@FullName, ',', ''), ' ', '.'), 2)
    end
    , case when parsename(replace(replace(@FullName, ',', ''), ' ', '.'), 3) is not null
        then parsename(replace(replace(@FullName, ',', ''), ' ', '.'), 1)
    end

But whenever there is a question about names in the database I feel I have to share this link. https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

Upvotes: 2

level3looper
level3looper

Reputation: 1051

You need a comma after ) AS LAST_NAME...

 ) AS LAST_NAME,

UPDATE: Below is your code with only one object. Can you confirm it works?

Select CASE WHEN 0 = CHARINDEX(' ',vp_timesheetpunch.personfullame)
     THEN NULL  --no more spaces?  assume rest is the first name
     ELSE SUBSTRING(
                      vp_timesheetpunch.personfullame,1
                     ,CHARINDEX(' ',vp_timesheetpunch.personfullame)-1
                   )  END AS MIDDLE_NAME
        ,SUBSTRING(
                      vp_timesheetpunch.personfullame,1 + CHARINDEX(' ',vp_timesheetpunch.personfullame)
                      ,LEN(vp_timesheetpunch.personfullame)
                  ) AS LAST_NAME
From vp_timesheetpunch

Upvotes: 1

Related Questions