Manipulating a Column Containing Key/Value Pairs
UPDATE
If as you posted in your own answer you're able to use a UDF to get specific key values let me suggest this one: (You don't need to split all key/values and you don't need to read the table again, you can get it by using text functions.)
CREATE FUNCTION fnGetKey(@Data text, @Key varchar(20))
RETURNS varchar(100)
AS
BEGIN
RETURN
(
SELECT
SUBSTRING (
@Data,
/* Position of first '=' after key + 1 */
CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data)) + 1,
/* Lenght, Position of first chr(13) after key less previuos value - 1 */
(CHARINDEX(CHAR(13), @Data, PATINDEX('%' + @key + '%', @Data))
-
CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data))) - 1
)
)
END
SELECT
FruitID, Name, Description,
dbo.fnGetKey([Data], 'key 2') as [key 2],
dbo.fnGetKey([Data], 'key 4') as [key 4]
FROM
[Fruit];
FruitID | Name | Description | key 2 | key 4 ------: | :----- | :---------- | :------ | :------ 1 | Banana | Delicious | value 2 | value 4 2 | Pear | Rotton | value 2 | value 4 3 | Kiwi | Okay | value 2 | value 4
db<>fiddle here
Original answer
The only solution I can figure out is by splitting key/values and then pivot it to obtain the desired result.
Unfortunately there are some inconveniences:
- STRING_SPLIT doesn't works with
text
columns. Hence you must cast it tovarchar
before you are able to manipulate it. - STRING_SPLIT requires a
nchar(1)
ornvarchar(1)
, ergo you should replaceCHAR(3)+CHAR(10)
by a single character. - Aggregate function on PIVOT works better with numeric values, then you should cast
Value
to some numeric data type. - PIVOT needs a well-known number of columns, in my example I have used a few of them but you should write the whole sequence unless you'd rather deal with dynamic queries.
This is what I've got using your sample data:
WITH KP AS
(
SELECT FruitID, Name, Description, value as KPair
FROM Fruit
CROSS APPLY STRING_SPLIT(REPLACE(CAST(Data AS varchar(max)), CHAR(13)+CHAR(10), ','), ',') /* STRING_SPLIT only allows nchar(1), varchar(1) */
)
, KP1 AS
(
SELECT
FruitID,
SUBSTRING(KPair, 5, CHARINDEX('=', KPair) - 5) AS [Key],
SUBSTRING(KPair, CHARINDEX('=', KPair) + 7, LEN(KPair) - CHARINDEX('=', KPair) - 6) AS [Value]
FROM
KP
)
SELECT [FruitID], [1],[2],[3],[4],[5]
FROM KP1
PIVOT (MAX([Value]) FOR [Key] IN ([1],[2],[3],[4],[5])) AS PVT;
First CTE split every Key X=Value Y
. The second one cut this value to obtain each [Key] and [Value]. And the final PIVOT compose the final result in columns.
FruitID | 1 | 2 | 3 | 4 | 5 ------: | :- | :- | :- | :- | :- 1 | 1 | 2 | 3 | 4 | 5 2 | 1 | 2 | 3 | 4 | 5 3 | 1 | 2 | 3 | 4 | 5
db<>fiddle here
NOTE: I'm not sure if I should maintain [Key 1] & [Value 1] or it should be converted as a column named [Key] & [Value].
A different approach
When I work with 3rd party databases I usually add a new database, on the same server/instance if possible, and then I use it for my own purposes, just to avoid conflicts with the DB owners.
In this case you could add a new table and periodically throw a process to update it with the new values.
You could use a table with all columns:
CREATE TABLE [FruitKeys]
(
[FruitID] int NOT NULL PRIMARY KEY,
[V1] int NULL,
[V2] int NULL,
[V3] int NULL,
[V4] int NULL,
[V5] int NULL
);
or a table with Key/Value pairs and use a pivot to obtain the final result:
CREATE TABLE [FruitKeys]
(
[FruitID] int NOT NULL,
[Key] int NOT NULL,
[Value] int NOT NULL,
CONSTRAINT [PK_FruitKeys] PRIMARY KEY ([FruitID], [Key])
);
It seems to me that the source data is not that far from JSON
format.
You could convert it pretty directly then use OPENJSON
to produce a relational output:
SELECT
F.FruitID,
F.[Name],
OJ.[key 1],
OJ.[key 2],
OJ.[key 3],
OJ.[key 4],
OJ.[key 5],
F.[Description]
FROM dbo.Fruit AS F
CROSS APPLY OPENJSON
(
-- Convert source data to JSON format
'{' +
CHAR(34) +
REPLACE
(
REPLACE
(
CONVERT(varchar(max), F.Data),
'=', CHAR(34) + ':' + CHAR(34)
),
CHAR(13) + CHAR(10),
CHAR(34) + ',' + CHAR(34)
) +
CHAR(34) +
'}'
)
WITH
(
[key 1] varchar(100),
[key 2] varchar(100),
[key 3] varchar(100),
[key 4] varchar(100),
[key 5] varchar(100)
) AS OJ;
Output:
FruitID | Name | key 1 | key 2 | key 3 | key 4 | key 5 | Description ------: | :----- | :------ | :------ | :------ | :------ | :------ | :---------- 1 | Banana | value 1 | value 2 | value 3 | value 4 | value 5 | Delicious 2 | Pear | value 1 | value 2 | value 3 | value 4 | value 5 | Rotton 3 | Kiwi | value 1 | value 2 | value 3 | value 4 | value 5 | Okay
db<>fiddle demo