t-sql get first item from list using substring and charindex

For those of us who have inherited non-normalized databases where lists are stored in a single column, it may be helpful to be able to get the first item in a list straight from your query. ColdFusion can do this by using ListFirst() once you return the query results, but I wanted to try to do the same in T-SQL. This would be useful if, for example, the first item in the list is the parent and the other items are children. The query below will help you identify the parent.

	DECLARE @InvalidList varchar(50)
	DECLARE @ValidList varchar(50)
	SET @InvalidList = 'Item1-Item2-Item3'
	SET @ValidList = 'Item1 > Item2 > Item3'
	SELECT
	CASE
	WHEN CHARINDEX('>',@InvalidList) = 0 THEN @InvalidList
	ELSE LTRIM(RTRIM(REPLACE(SUBSTRING(@InvalidList,1,CHARINDEX('>',@InvalidList)),'>','')))
	END
	SELECT
	CASE
	WHEN CHARINDEX('>',@ValidList) = 0 THEN @ValidList
	ELSE LTRIM(RTRIM(REPLACE(SUBSTRING(@ValidList,1,CHARINDEX('>',@ValidList)),'>','')))
	END

view raw tsql_GetFirstItemFromListUsingSubstringAndCharIndex hosted with ❤ by GitHub

The first query which uses @InvalidList, will return 'Item1-Item2-Item3' because it does not match the list delimiter. The second query which uses @ValidList, will return 'Item1' only since it matched the list delimiter. The REPLACE is used to obviously strip the delimiter. Note the use of LTRIM and RTRIM to trim all spaces so our value is clean.