T SQL Extract String between penultimate and last comma

I’m using MS SQL Server 2014 SP3.

I have a column called person_loader that contains one large string. I have no control over this as its from a 3rd party system.

Sample data:

1. Bob Smith, 01/01/1980, "email: bob@test.com, mobile: 012345687",USA, Joiner, 05/04/2022
2. Dolly Smith, 02/03/1978, "email: dolly@test.com", UK, Singer,
3. Dave Smith, 09/08/78,"mobile: 98745632", USA, Unemployed, 04/04/2022
4. Bud Smith, 07/07/80,"email:bud.smith@test.com, mobile: 0147852369", UK, Dr,

I want to extract the string between the penultimate and last ‘,’. Here is the result:

1. Joiner
2. Singer
3. Unemployed
4. Dr

Sometimes the string won’t end with the date, but there will always be a comma.

I can extract everything right of the last comma, but how do I build on this?

SELECT RIGHT([person_loader], CHARINDEX(',', REVERSE([person_loader])) - 1)
FROM tblCustomer;

>Solution :

In newer (and supported) versions of SQL Server, this can be much easier with, say, OPENJSON. In older, unsupported versions, you’re stuck with ugly string parsing… not one of SQL Server’s strong suits.

;WITH level1 AS
(
  SELECT pl = SUBSTRING
         (
           person_loader, 
           1, 
           LEN(person_loader) - CHARINDEX(',', REVERSE(person_loader))
         )
    FROM dbo.tblCustomer
)
SELECT LTRIM(RIGHT(pl, CHARINDEX(',', REVERSE(pl))-1)) FROM level1;

Output:

• Example db<>fiddle

This will fail, of course, if there are strings in the table with one or zero commas. You can deal with this (along with empty strings and NULL) using a bunch of additional (and even uglier) COALESCE(NULLIF( handling:

;WITH level1 AS
(
  SELECT pl = SUBSTRING
    (
      person_loader, 
      1, 
      LEN(person_loader) 
        - COALESCE(NULLIF(CHARINDEX(',', 
          REVERSE(person_loader)),0),0)
    )
    FROM dbo.tblCustomer
)
SELECT COALESCE(LTRIM(RIGHT(pl, 
  COALESCE(NULLIF(CHARINDEX(',', 
  REVERSE(pl)), 0),1)-1)), '')
FROM level1;