How to split cell contents and extract information into a new column in SQL select statement?

Question

I have a table in which one of the columns stores key / value pairs separated by a semicolon, for example:

KEY1:VALUE1;KEY2:VALUE2;KEY3:VALUE3 

I would like to build a view where I have additional columns where the value will be extracted from the field above. My question is how to extract VALUE1, VALUE2 and VALUE3 in a SELECT query.

This field will not have more than three key-value pairs.

Answer 1

I already answered an incredibly similar question today, so look at the answer:

SQL comma column => to rows, then sum total?

but try the following:

I prefer a number table approach to split a string in TSQL

For this method to work, you need to complete this setup at once:

 SELECT TOP 10000 IDENTITY(int,1,1) AS Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number) 

Once the Numbers table is configured, create this split function:

 CREATE FUNCTION [dbo].[FN_ListToTable] ( @SplitOn char(1) --REQUIRED, the character to split the @List string on ,@List varchar(8000)--REQUIRED, the list to split apart ) RETURNS TABLE AS RETURN ( ---------------- --SINGLE QUERY-- --this will not return empty rows ---------------- SELECT ListValue FROM (SELECT LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue FROM ( SELECT @SplitOn + @List + @SplitOn AS List2 ) AS dt INNER JOIN Numbers n ON n.Number < LEN(dt.List2) WHERE SUBSTRING(List2, number, 1) = @SplitOn ) dt2 WHERE ListValue IS NOT NULL AND ListValue!='' ); GO 

Now you can easily split the CSV row into a table and join it:

 select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,') 

CONCLUSION:

 ListValue ----------------------- 1 2 3 4 5 6777 (6 row(s) affected) 

Now you can use CROSS APPLY to split each row in the table, for example:

 DECLARE @YourTable table (RowID int, RowValue varchar(200)) INSERT INTO @YourTable VALUES (1,'KEY11:VALUE11;KEY12:VALUE12;KEY13:VALUE13') INSERT INTO @YourTable VALUES (2,'KEY21:VALUE21;KEY22:VALUE22;KEY23:VALUE23') INSERT INTO @YourTable VALUES (3,'KEY31:VALUE31;KEY32:VALUE32;KEY33:VALUE33') SELECT o.RowID,RIGHT(st.ListValue,LEN(st.ListValue)-CHARINDEX(':',st.ListValue)) AS RowValue FROM @YourTable o CROSS APPLY dbo.FN_ListToTable(';',o.RowValue) AS st 

CONCLUSION:

 RowID ----------- ------- 1 VALUE11 1 VALUE12 1 VALUE13 2 VALUE21 2 VALUE22 2 VALUE23 3 VALUE31 3 VALUE32 3 VALUE33 (9 row(s) affected) 

Answer 2

Writing a scalar function that receives a string containing key / value pairs and an index of the value to receive (or a key of the value to receive) as its arguments and returns the corresponding value will make your problem easily solvable.

You can also write this scalar function in .Net, which will work much better than writing it in TSQL, since you will not need to access any tables or database objects from this function.