1

I have a table where one of the columns are storing key/value pairs separated by semicolons, like this: 

KEY1:VALUE1;KEY2:VALUE2;KEY3:VALUE3

I would like to construct 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.

There will not be more than three key-value pairs in this field.

gyurisc
  • 11,234
  • 16
  • 68
  • 102
  • 4
    that means your database is not in 3NF ... – Mitch Wheat Feb 26 '10 at 14:59
  • Is the number of key/value pairs limited to three, or can there be any number? – Ed Harper Feb 26 '10 at 15:00
  • I know but it it is flexible this way then adding field for each possible value-key pair. – gyurisc Feb 26 '10 at 15:01
  • if it is limited to three, why not just have six columns: key1, vaule1, key2, value2, key3, value3??? – KM. Feb 26 '10 at 15:03
  • @KM It never occured to me, I guess. :) I just created an attribute column because I knew that I will need one, but never really considered how many of them I will actually need. – gyurisc Feb 26 '10 at 15:11
  • 1
    It may be flexible, but as it's not in 3NF, you will get querying problems like this over and over and over, and once you start adding the necessary parsing functions to all your queries, you're bound to hit performance issues as well (unless it's a small database). – Philip Kelley Feb 26 '10 at 15:11
  • @Philip The purpose of this table is to collect information from events in my system, thus I do not want to risk slowing down the overall system with updating multiple table. I can extract the data later when it is being analized. – gyurisc Feb 26 '10 at 15:19
  • I've updated my answer with the actual code needed for your problem – KM. Feb 26 '10 at 15:46

2 Answers2

1

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

SQL comma delimted column => to rows then sum totals?

but try this:

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

For this method to work, you need to do this one time table setup:

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 set up, 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

You can now easily split a CSV string into a table and join on it:

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

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can now use a CROSS APPLY to split every row in your table like:

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

OUTPUT:

RowID       
----------- -------
1           VALUE11
1           VALUE12
1           VALUE13
2           VALUE21
2           VALUE22
2           VALUE23
3           VALUE31
3           VALUE32
3           VALUE33

(9 row(s) affected)
Community
  • 1
  • 1
KM.
  • 101,727
  • 34
  • 178
  • 212
1

Writing a scalar function, which receives the string containing the key/value pairs, and the index of the value to get (or the key of the value to get) as its arguments, and returns the appropriate value, would make your problem easily solvable.

You could also write this scalar function in .Net, which would perform a lot better than writing it in TSQL, as you wouldn't have to access any tables or database objects from that function.

treaschf
  • 5,788
  • 1
  • 25
  • 24
  • Do you have a sample or tutorial for writing this kind of functions in .NET? I never tried it and I would love to. – gyurisc Mar 01 '10 at 19:25
  • In Visual Studio you have to create a new project (C# projects, Database, Sql Server Project). Then add into the project a new User-defined function from Solution Explorer, and you will get a "hello world" example, to start with. – treaschf Mar 03 '10 at 12:46