[SQL] Tags parsen en result weergeven

[Reveller]

In een oud CRM systeem kunnen klantmanagers metadata aan users toevoegen door tags te schrijven in een freetext comment field:

code:

id     username     comment
----------------------------------------------------------------
 1     peter        [afdeling:A] [allergie:Noten] [geslacht:Man]
 2     mieke        [afdeling:B] [geslacht:Vrouw]

Ik wil deze data opvragen, parsen en in een #temp table stoppen, als volgt:

code:

id     username     afdeling  allergie   geslacht
-------------------------------------------------
 1     peter        A         Noten      Man
 2     mieke        B         NULL       Vrouw

Verduidelijking van de situatie aan de hand van bovenstaand voorbeeld:

• Het aantal [tag:]s per user kan verschillen
• Niet alle users hebben dus alle [tag:]s
• Het is prima om een aantal [tag:]s te hardcoden. Code hoeft niet dynamisch alle tags op te pakken. Ik ben daarom begonnen met de definitie van de temp table:

SQL:

create table #users
(
    id int identity
  , username nvarchar(50)
  , afdeling nvarchar(50)
  , allergie nvarchar(50)
  , geslacht nvarchar(1)
)

Maar hier loop ik vast. Hoe vul ik deze table?

• Met een sequentie van replaces en substrings zoeken naar de value (wordt al snel onoverzichtelijk)
• Met een aparte functie?

Graag input, want mijn SQL kennis is beperkt en mss ligt het antwoord wel om de hoek

[Rannasha]

Ik heb niet zo 1-2-3 een goede manier om de tags te parsen, maar voor het maken van de temp table zou ik een ander structuur overwegen:

code:

id     tagnaam     tagwaarde
-------------------------------
1      afdeling    A
1      geslacht    Man
1      allergie    Noten
2      afdeling    B
2      geslacht    Vrouw

Voordeel hiervan is dat je veel algemener bezig bent, je hoeft de tags niet te hardcoden in je parse-query, waardoor je makkelijk later op andere tags kunt zoeken dan je in eerste instantie dacht.

Wat het parsen betreft, zoek eens op iets als "SQL tokenize string" voor wat code om de comment string op te splitsen (eerst splitsen op spaties om de verschillende tags te scheiden, daarna splitsen op de dubbele punt om tagnaam van tagwaarde te scheiden).

[Skinny]

Voortbordurend op Rannasha (en het feit dat ik een broodje zat te eten)

SQL:

CREATE FUNCTION dbo.CreateTags(
    @input varchar(100),
    @tag_delimiter nvarchar(3) = ' ',
    @key_value_delimiter nvarchar(3)= ':'
)
RETURNS @Tags table 
(
Tag nvarchar(255),
Value nvarchar(255)
) 
AS
BEGIN

DECLARE @Tokens table (Token nvarchar(100), DelimiterPosition int)


WHILE (CHARINDEX(@tag_delimiter,@input)>0) -- loop through input string and insert every token/tag into temp table @Tokens
BEGIN
    INSERT INTO @Tokens (Token, DelimiterPosition) VALUES (LTRIM(RTRIM(SUBSTRING(@input,1,CHARINDEX(@tag_delimiter,@input)-1))) , 0)
    SET @input = LTRIM(RTRIM(SUBSTRING(@input, CHARINDEX(@tag_delimiter,@input)+LEN(@tag_delimiter),LEN(@input))))
END

INSERT INTO @Tokens (Token) VALUES (@input) -- insert the last / default value

UPDATE @Tokens SET DelimiterPosition = CHARINDEX(@key_value_delimiter, TOKEN) -- calculate delimiter position up front to avoid x times charindex

INSERT INTO @Tags (Tag, Value)
SELECT 
    SUBSTRING(Token,2,DelimiterPosition-2),
    SUBSTRING(Token,DelimiterPosition+1, LEN(Token)-DelimiterPosition-1 )
FROM @Tokens
WHERE DelimiterPosition > 0


RETURN 
END

Have fun
Een functie die de gegeven input string (wel nog zonder error checking, oefening voor jou) een table met Tag-Value pairs retourneert.

Vervolgens CROSS APPLY je die op je users tabel en krijg je

SQL:

SELECT * 
FROM Users u
CROSS APPLY dbo.Tokenize(u.comments,default,default)

code:

id  username    comments    Tag Value
1   peter    [afdeling:A] [allergie:Noten] [geslacht:Man]   afdeling    A
1   peter    [afdeling:A] [allergie:Noten] [geslacht:Man]   allergie    Noten
1   peter    [afdeling:A] [allergie:Noten] [geslacht:Man]   geslacht    Man
2   mieke   [afdeling:B] [geslacht:Vrouw]   afdeling    B
2   mieke   [afdeling:B] [geslacht:Vrouw]   geslacht    Vrouw

Let er nog wel even op dat dit soort loops/string operaties in SQL relatief traag is, dus ga dit niet in standaard queries inbouwen die je op grote tabellen of in hoge frequentie aanroept.

[ Voor 4% gewijzigd door Skinny op 11-03-2016 12:40 ]

[NMe]

Is er nog een bijzondere reden waarom je, ook al is het maar een temp table, niet normaliseert? Dat maakt meteen je probleem ook een stuk simpeler.

[Reveller]

NMe schreef op vrijdag 11 maart 2016 @ 12:44:
Is er nog een bijzondere reden waarom je, ook al is het maar een temp table, niet normaliseert? Dat maakt meteen je probleem ook een stuk simpeler.

Die bijzondere reden is onervarenheid, denk ik dan maar

Ik zie eerlijk gezegd niet hoe ik dan zou normaliseren, of in ieder geval..hoe dat het probleem (= parsen van de string) eenvoudiger zou maken?

[NMe]

Reveller schreef op vrijdag 11 maart 2016 @ 16:53:
[...]

Ik zie eerlijk gezegd niet hoe ik dan zou normaliseren, of in ieder geval..hoe dat het probleem (= parsen van de string) eenvoudiger zou maken?

Het maakt niet het parsen makkelijker, het maakt de query's die je daarna moet doen makkelijker. Kolomnamen baseren op variabelen (ook als die semi-vast staan) is niet de meest intuïtieve manier van werken.