Tag Archives: split value

Using Charindex + Cross Apply to split delimiter-separated values (SQL Server)

Let’s suppose that we have some delimiter-separated values, like this:

charindex01

We want to separate the delimited values into five columns, like this:

charindex02

We can solve this using the following code:

DECLARE @States TABLE
(StateList VARCHAR(50));


DECLARE
 @delimiter CHAR(1);

SET @delimiter = ',';

INSERT INTO @States VALUES
('Florida,Indiana,Kansas,Georgia,Alabama'),
('New York,California,Nevada,Texas,Montana');

SELECT * FROM @States;SELECT
SUBSTRING(StateList,1,T1.Position-1) S1,
SUBSTRING(StateList,T1.Position+1,T2.Position-T1.Position-1) S2,
SUBSTRING(StateList,T2.Position+1,T3.Position-T2.Position-1) S3,
SUBSTRING(StateList,T3.Position+1,T4.Position-T3.Position-1) S4,
SUBSTRING(StateList,T4.Position+1,LEN(StateList)) S5
FROM @States
--1st delimiter position 
CROSS APPLY (SELECT (CHARINDEX(@delimiter,StateList))) as T1(Position)
--2nd delimiter position
CROSS APPLY (SELECT (CHARINDEX(@delimiter, StateList, T1.Position+1))) as T2(Position)
--3th delimiter position
CROSS APPLY (SELECT (CHARINDEX(@delimiter, StateList, T2.Position+1))) as T3(Position)
--4th delimiter position
CROSS APPLY (SELECT (CHARINDEX(@delimiter, StateList, T3.Position+1))) as T4(Position);