sql server string split last but one

Question

Table has a column with values

 ColA
------

a.b.c.d.e (car.make.model, car.la, kg)

ab.cd.ef (car.make.model)

a1.b2.c3.d4.e5(car.make.model, car.la, kg, av.vc.de)

I want to write a sql query to split the ColA by delimiter "." and pick last but one.

Expected output

Result
------
d

cd

d4

I have tried ParseName but dont see option to pick last but one.

Thank you

Possible duplicate of [Is there a LastIndexOf in SQL Server?](https://stackoverflow.com/questions/39002025/is-there-a-lastindexof-in-sql-server) — John Bustos, May 23 '18 at 14:57
What's the purpose of the additional data posted? They don't seem to influence the expected result. — Giorgos Betsos, May 23 '18 at 15:30

score 3 · Answer 1 · answered May 23 '18 at 14:48

Using Jeff Moden's DelimitedSplit8K:

USE Sandbox;
GO

CREATE TABLE #Sample (ColA varchar(500));
GO
INSERT INTO #Sample
VALUES ('a.b.c.d.e'),
       ('ab.cd.ef'),
       ('a1.b2.c3.d4.e5');
GO

SELECT *
FROM #Sample;

WITH Split AS(
    SELECT S.ColA,
           DS.*,
           MAX(DS.ItemNumber) OVER (PARTITION BY S.ColA) AS Items
    FROM #Sample S
         CROSS APPLY DelimitedSplit8K(S.ColA,'.') DS)
SELECT Item
FROM Split
WHERE ItemNumber = Items - 1;

GO

DROP TABLE #Sample

Ideally, though, don't store your data in a delimited format. :)

+1 for a great answer. +1000000 for this "Ideally, though, don't store your data in a delimited format.". — Sean Lange, May 23 '18 at 14:53

Tyron78 · Answer 2 · 2018-05-23T15:59:25.140

Why not simply using substring?

DECLARE @ColA NVARCHAR(100) = 'a1.b2.c3.d4.e5(car.make.model, car.la, kg, av.vc.de)';
SELECT REVERSE(LEFT(RIGHT(REVERSE(LEFT(@ColA, CHARINDEX('(', @ColA)-1)), LEN(LEFT(@ColA, CHARINDEX('(', @ColA)-1))-CHARINDEX('.',REVERSE(LEFT(@ColA, CHARINDEX('(', @ColA)-1)))), CHARINDEX('.',RIGHT(REVERSE(LEFT(@ColA, CHARINDEX('(', @ColA)-1)), LEN(LEFT(@ColA, CHARINDEX('(', @ColA)-1))-CHARINDEX('.',REVERSE(LEFT(@ColA, CHARINDEX('(', @ColA)-1)))))-1))

However, this last edit does NOT handle the case when there is no . or no ( in the string - feel free t o extend the query accordingly

@sri I modified the query according to your last edit – Tyron78 May 23 '18 at 16:00 — Tyron78, May 23 '18 at 16:00

Giorgos Betsos · Answer 3 · 2018-05-23T15:23:25.440

Just to play around using STRING_SPLIT:

SELECT ColA, t.value
FROM table1
CROSS APPLY(SELECT value, 
                   COUNT(*) OVER () as cnt,
                   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
            FROM STRING_SPLIT(ColA, '.')) AS t
WHERE t.rn = t.cnt - 1

Note: The function is available from SQL Server 2016.

Note 2: The query works provided that the function returns each value in the same order as the one it appears inside the string.

score 0 · Answer 4 · answered May 23 '18 at 14:59

Try This

;WITH CTE(ColA)
AS
(
SELECT 'a.b.c.d.e'      UNION ALL
SELECT 'ab.cd.ef'       UNION ALL
SELECT 'a1.b2.c3.d4.e5'
)
SELECT ColA,REVERSE(SUBSTRING(ReqColA,0,CHARINDEX('.',(ColA)))) AS ReqColA
  FROM
(
SELECT ColA ,SUBSTRING(REVERSE(ColA),CHARINDEX('.',REVERSE(ColA))+1,LEN(REVERSE(ColA))) AS ReqColA  FROM CTE
)dt

Result

ColA            ReqColA
-----------------------
a.b.c.d.e       d
ab.cd.ef        cd
a1.b2.c3.d4.e5  d4

sql server string split last but one

4 Answers4