How to extract all values except the last value in a string separated by comma in sql

Transact-SQL
1

I am think of SUBSTRING_INDEX but dont know how to extract all values except the last value in a string separated by comma

e.g :
value is "aaa,bbb,ccc,ddd"
desired result is "aaa,bbb,ccc"

e.g :
value is "a1,b2,c3,d4,e5"
desired result is "a1,b2,c3,d4"

2 
/* *** Test Data *** */
CREATE TABLE #t
(
	YourString varchar(255) NOT NULL
);
GO
INSERT INTO #t
VALUES ('aaa,bbb,ccc,ddd')
	,('a1,b2,c3,d4,e5');
GO
/* *** End Test Data *** */

SELECT REVERSE(SUBSTRING(R.YourString, CHARINDEX(',', R.YourString) + 1, 255)) AS YourResult
FROM #t T
	CROSS APPLY(VALUES(REVERSE(T.YourString))) R (YourString);
2 Likes