Det finns flera fall där man behöver dela upp en sträng i flera delar. En anledning kan vara att man behöver en flexibel parameter till en lagrad procedur. En annan kan vara att man arbetar med en tabell som inte är normaliserad.
Jag påträffade en annan sorts problem i vintras när jag arbetade med en stor kund. Deras applikation lämnade ifrån sig en sträng till databasen och strängens innehåll var beroende på vad användarna gjort för val i klienten med hänsyn till checkboxar, fritextrutor och annat. Dessa värden skulle sedan in till en lagrad procedur för att bygga ihop en dynamisk fråga. Jag lyckades till slut att göra proceduren 10 000 000 gånger snabbare och en av faktorerna (förutom omdesign av datamodellen) var att hantera den sträng som kom från klienten på ett effektivt sätt som möjligt. Vill du veta mer om listor i SQL Server kan du läsa här http://www.sommarskog.se/arrays-in-sql.html.
Som de flesta vet är just hantering av strängar inte en av SQL Servers styrkor. Men det finns sätt att göra det effektivt på! Vad jag kommer att visa nu är en funktion som hanterar multipla attribut med flerval samt wildcard.
Ett typisk strängvärde kan se ut som “PersonID=201304061234“. Vad den informationen betyder är att kolumnen PersonID ska vara lika med det personnummer som skickades med. Det här är ganska trivialt att dela upp med T-SQL. Strängen kan också se ut så här “Utbildningsnivå=Kandidat|Stad=Malmö”. De olika attributen kan alltså vara uppdelade med ett pipe-tecken.
I min kunds fall visade det sig dessutom att det finns flera olika klienter som anropade den aktuella proceduren. Från en klient kan strängen se ut så här “Medlemskap=Guld|Medlemskap=Silver|Inkasso=Nej” medan den kan se ut så här “Medlemskap=Guld,Silver|Inkasso=Nej” från en annan klient. Till en början innebar det naturligtvis en utmaning men när du tittar närmare på de kombinationer som finns, uppdagar ett mönster sig.
- De olika attributen är uppdelade med “|”
- Attribut och värde är uppdelat med “=”
- Flervalsmöjligheter är uppdelade med “,”
Finns det ett samband mellan de fyra olika stränginnehållen ovan? Ja, det finns det faktiskt. Om vi börjar i andra ändan, dvs med det slutresultat vi vill ha, så syns det ganska tydligt.
|
Attribut |
Värde |
| PersonID | 201304061234 |
|
Attribut |
Värde |
| Utbildningsnivå | Kandidat |
| Stad | Malmö |
|
Attribut |
Värde |
| Medlemskap | Guld |
| Medlemskap | Silver |
| Inkasso | Nej |
Aha! Det som ska göras är en normalisering av stränginnehållet. När denna insikt lagt sig startade jag att fundera på hur man på enklast möjliga sätt ska skriva en rutin för att dela på strängen på det önskade viset. Redan i planeringsstadiet föll en iterativ process bort, mest med tanke på komplexiteten att hantera komma eller inte komma och när kommer pipen. Likamedtecknet är det enklaste i detta fallet.
Precis om jag redovisade ovan gjorde jag också på papper hos kunden. När jag brottades med hur jag skulle likställa attributet Medlemskap mellan de olika klienterna insåg jag att en OUTER APPLY skulle göra hela skillnaden! Och då föddes tanken med att skriva funktionen som en Inline Table-Valued Function (IVTF) för absolut bästa prestanda. För att underhållet av funktionen skulle krävas ett minimum av specialkunskaper valde att jag att arbeta med XML.
Första åtgärden var att dela upp strängen med pipetecknet för att separera de olika attributen. Vad jag ville åstadkomma var ett XML-dokument som såg ut så här
<r>
<c>Medlemskap=Guld,Silver</c>
</r>
<r>
<c>Inkasso=Nej</c>
</r>
Den biten är ganska lätt. Jag gjorde en REPLACE på “|”till de olika xml-elementen med hjälp av den här statementet
SELECT CAST(‘<r><c>’ + REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’) + ‘</c></r>’ AS XML)
Men det är inte tillräckligt. Jag vill ju även dela upp strängen i attribut och värden som olika kolumner. Det kan vi göra genom att lägga till en extra REPLACE i samma statement! Då ser statementet ut så här
SELECT CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML)
och producerar ett XML-dokument som ser ut så här
<r>
<c>Medlemskap</c>
<c>Guld,Silver</c>
</r>
<r>
<c>Inkasso</c>
<c>Nej</c>
</r>
Vid en första anblick kan det se konstigt ut att jag benämner de båda kolumnerna som “c”. Tänk då på att vi arbetar med XML och vi kan särskilja dessa med deras inbördes positioner. Detta är i normala fall inget som ska ha en betydelse men för den här funktionen måste det ha en betydelse då vi inte vet om det ens finns ett värde för attributet.
Sätter vi ihop statementet till en fungerande fråga som ska fungera som bas för funktionen får vi skriva en wrapper och hantera utdatat som XML.
SELECT d.Data
FROM (
VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
) AS d(Data)
I detta läge vill vi omvandla XML-dokumentet i mindre dokument, eftersom varje nod “r” ska bli en minst en rad i resultatsetet. Det gör vi genom att dela upp XML-dokumentet med en CROSS APPLY. Här kan vi använda CROSS APPLY eftersom det måste finnas minst en nod “r” för annars är XML-dokumentet tomt.
SELECT r.n.query(‘.’)
FROM (
VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
) AS d(Data)
CROSS APPLY d.Data.nodes(‘(r)’) AS r(n)
Nu har vi två XML-dokument som ser ut så här
<r>
<c>Medlemskap</c>
<c>Guld,Silver</c>
</r>
<r>
<c>Inkasso</c>
<c>Nej</c>
</r>
Nu kommer det att bli lite svårare eftersom vi ska kontrollera om värdet är ett flerval genom att dela upp nod “c” i två delar igen. Och här får vi ju inte dela upp attributet med hjälp av komma! För att särskilja de två noderna “c” mellan attribut och värde använder vi deras inbördes ordning med hjälp av xquery-noteringen “[]”.
SELECT r.n.query(‘.’),
f.n
FROM (
VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
) AS d(Data)
CROSS APPLY d.Data.nodes(‘(r)’) AS r(n)
CROSS APPLY (
VALUES (CAST(‘<r>’ + REPLACE(r.n.value(‘(c[2])’, ‘VARCHAR(MAX)’), ‘,’, ‘</r><r>’) + ‘</r>’ AS XML))
) AS f(n)
Detta statement ger ett resultatset som har två kolumner
<r><c>Medlemskap</c><c>Guld,Silver</c></r> <r>Guld</r><r>Silver</r>
<r><c>Inkasso</c><c>Nej</c></r> <r>Nej</r>
Vad vi nu behöver är en sista uppdelning på den andra kolumnen och dela upp noderna “r”. Denna gång använder vi OUTER APPLY eftersom vi inte vet om det ens finns ett värde för attributet.
SELECT r.n.query(‘.’),
f.n,
x.n.query(‘.’)
FROM (
VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
) AS d(Data)
CROSS APPLY d.Data.nodes(‘(r)’) AS r(n)
CROSS APPLY (
VALUES (CAST(‘<r>’ + REPLACE(r.n.value(‘(c[2])’, ‘VARCHAR(MAX)’), ‘,’, ‘</r><r>’) + ‘</r>’ AS XML))
) AS f(n)
OUTER APPLY f.n.nodes(‘(r)’) AS x(n)
OUTER APPLY gör att vi nu får tre rader i resultatsetet (Andra och tredje kolumnen)
<r>Guld</r><r>Silver</r> <r>Guld</r>
<r>Guld</r><r>Silver</r> <r>Silver</r>
<r>Nej</r> <r>Nej</r>
Nu är det klart och vi kan skriva funktionen så här
CREATE FUNCTION dbo.fnTokenizeAttributes
(
@AttributeList VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN (
WITH cteAttributes(KeyType, KeyValue)
AS (
SELECT ISNULL(r.n.value(‘(c[1])’, ‘VARCHAR(48)’), ”) AS KeyType,
ISNULL(x.n.value(‘(text()[1])’, ‘VARCHAR(512)’), ”) AS KeyValue
FROM (
VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
) AS d(Data)
CROSS APPLY d.Data.nodes(‘(r)’) AS r(n)
CROSS APPLY (
VALUES (CAST(‘<r>’ + REPLACE(r.n.value(‘(c[2])’, ‘VARCHAR(MAX)’), ‘,’, ‘</r><r>’) + ‘</r>’ AS XML))
) AS f(n)
OUTER APPLY f.n.nodes(‘(r)’) AS x(n)
)
SELECT DISTINCT LTRIM(RTRIM(KeyType)) AS KeyType,
LTRIM(RTRIM(KeyValue)) AS KeyValue
FROM cteAttributes
WHERE LTRIM(RTRIM(KeyType)) > ”
)
GO
Vad jag också gjorde i funktionen var att sätta saknade kolumner med NULL-värde till tomsträng. Detta gör jag eftersom det kan finnas attribut utan värden. Dessutom kontrollerar jag att inga rader med tomma attribut returneras samt tar bort alla dubbletter.
