Tag: SQL server

How to split string in SQL Server

There could be many method but here are two simple one;

DECLARE @ValueToSplit NVARCHAR(50) = N'12345VA [987]'

--This is classical method to split string
SELECT
	SUBSTRING(@ValueToSplit, 0, CHARINDEX('[', @ValueToSplit)) AS [FIRST],
	SUBSTRING(@ValueToSplit, CHARINDEX('[', @ValueToSplit)+1, LEN(@ValueToSplit)) AS [SECOND]

--This function can be used in SQL 2016 onward
SELECT split.*
FROM
(
	SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RowNumber, value AS TextValue FROM STRING_SPLIT(@ValueToSplit,'[')
) split
WHERE 1=1
AND split.RowNumber = 1

Here are the results;