SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ListSplitName (Name VARCHAR(255));

INSERT INTO ListSplitName
VALUES
    ('Inv 3225366, BUK-100694, London Distribution Solutions B, PICKING 37,531 @£0.20');
1 rows affected

/* 2022 */
SELECT lsn.Name
  , TRIM(s.value) AS [BUK]
  , s.ordinal
FROM ListSplitName lsn
  CROSS APPLY STRING_SPLIT(lsn.Name, ',', 1) s
WHERE s.ordinal = 2;


Name
BUK
ordinal


Inv 3225366, BUK-100694, London Distribution Solutions B, PICKING 37,531 @£0.20
BUK-100694
2


/* 2017 */
SELECT *
FROM
  (
    SELECT lsn.Name
      , TRIM(s.value) AS [BUK]
      , ROW_NUMBER() OVER (PARTITION BY lsn.Name ORDER BY (SELECT NULL)) AS ordinal
    FROM ListSplitName lsn
      CROSS APPLY STRING_SPLIT(lsn.Name, ',') s
  ) i
WHERE i.ordinal = 2;


Name
BUK
ordinal


Inv 3225366, BUK-100694, London Distribution Solutions B, PICKING 37,531 @£0.20
BUK-100694
2


/* 2016 */
SELECT *
FROM
  (
    SELECT lsn.Name
      , LTRIM(RTRIM(s.value)) AS [BUK]
      , ROW_NUMBER() OVER (PARTITION BY lsn.Name ORDER BY (SELECT NULL)) AS ordinal
    FROM ListSplitName lsn
      CROSS APPLY STRING_SPLIT(lsn.Name, ',') s
  ) i
WHERE i.ordinal = 2;


Name
BUK
ordinal


Inv 3225366, BUK-100694, London Distribution Solutions B, PICKING 37,531 @£0.20
BUK-100694
2