T SQL Extract String between penultimate and last comma-CodePudding

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: [email protected], mobile: 012345687",USA, Joiner, 05/04/2022
2. Dolly Smith, 02/03/1978, "email: [email protected]", UK, Singer,
3. Dave Smith, 09/08/78,"mobile: 98745632", USA, Unemployed, 04/04/2022
4. Bud Smith, 07/07/80,"email:[email protected], 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;

CodePudding user response：

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:

(No column name)
Joiner
Singer
Unemployed
Dr

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;