UDF: Create a single-column user-defined table from a delimited string
Παραθέτω μια απλή User Defined Function για το "σπάσιμο" ενός delimited string σε ένα table variable μιας στήλης, ο οποίος περιέχει σε κάθε γραμμή ένα στοιχείο της delimited string. Ειναι δυνατός προαιρετικά ο προσδιορισμός του του delimeter.
Στο συγκεκριμένο παράδειγμα έχουμε default delimeter το κόμμα (,) ενώ το μέγιστο μήκος του κάθε στοιχείου έχει οριστεί στους 250 χαρακτήρες. Ακόμα, υποθέτουμε οτι το delimited string που περνάμε είναι τύπου TEXT, γι'αυτό χρησιμοποιείται η συνάρτηση DATALENGTH αντί της κλασικής LENGTH. Εύκολα μπορείτε να επεξεργαστείτε τον κώδικα ωστε να ορίσετε το δικό σας μέγιστο μήκος ή / και να χειριστείτε απλές varchar strings αντί για text.
/*
Η UDF αυτή παίρνει ένα delimited string για το οποίο προσδιορίζουμε
το delimeter character και επιστρέφει ένα table variable
που περιέχει μία μόνο στήλη με ένα element που εξήχθη από το string σε κάθε γραμμή.
Εχουμε κάνει κάποιες υποθέσεις: Για παράδειγμα, θεωρούμε οτι το delimited string μας
θα είναι TEXT (δεν θα περιορίζεται δηλ. στους 8000 χαρακτήρες) και ότι από την άλλη μεριά
κάθε item θα έχει μήκος το πολύ 250 χαρακτήρες.
*/
CREATE Function dbo.getTableFromDelimitedString
(@str text ,@delimiter varchar(1)=',' )
RETURNS @Table TABLE (splitValue varchar(250))
AS
BEGIN
DECLARE @searchOffset int
DECLARE @lastFoundPosition int
/*
Προσοχή: Το @extractedValue πρέπει να έχει το ίδιο datatype και μήκος όπως και
το πεδίο splitValue του table variable.
*/
DECLARE @extractedValue varchar(250)
--Για λόγους ασφαλείας, απλά τσεκάρουμε οτι το string υφίσταται. :)
IF DATALENGTH(@str)>0 BEGIN
SET @searchOffset=1 --Αυτό είναι το offset από την αρχή του string στο οποίο ψάχνουμε.
SET @lastFoundPosition=0 --Αυτή είναι η τελευταία θέση που βρήκαμε delimiter character.
--Ας ξεκινήσουμε λοιπόν ένα loop...
WHILE @lastFoundPosition < DATALENGTH (@str)
BEGIN
/*
Ψάχνουμε να βρούμε το delimiter character μέσα στο string και συγκεκριμένα
ξεκινώντας μία θέση μετά σημείο οπου βρήκαμε τον τελευταίο delimiter string
(ή τον χαρακτήρα 1 αν βρισκόμαστε στην αρχή)
*/
SET @lastFoundPosition = CHARINDEX (@delimiter, SUBSTRING (@str, @searchOffset, DATALENGTH (@str)), 0)
/*
Αν δεν βρήκαμε τίποτα, τότε πάμε το δείκτη @lastFoundPosition σε μια τιμή
που θα μας βγάλει από το loop.
*/
IF @lastFoundPosition = 0
BEGIN
SET @lastFoundPosition=DATALENGTH (@str)+1
END
/*
Παίρνουμε την τιμή που βρήκαμε μεταξύ της αρχής της τελευταίας αναζήτησης
μέχρι ενα χαρακτήρα πίσω από τη θέση που βρήκαμε το delimiter character.
Αυτό είναι και η επόμενη τιμή που θα βάλουμε στο user defined table.
Φυσικά, αν δεν έχουμε άλλο delimiter character, τότε εδώ θα αντληθεί το
υπόλοιπο του string (η τελευταία τιμή, ουσιαστικά).
*/
SET @extractedValue=SUBSTRING (@str, @searchOffset, @lastFoundPosition-1)
-- Και φυσικά, το βάζουμε στο table.
INSERT INTO @Table VALUES (@extractedValue)
/*
To loop θα συνεχιστεί θέτοντας το @searchOffset (το offset μας από την αρχή του
string) στη θέση στην οποία βρήκαμε τον τελευταίο delimiter character.
Στο επόμενο iteration, η αναζήτηση θα συνεχιστεί από αυτό το σημείο του
string και μετά.
*/
SET @searchOffset=@searchOffset+@lastFoundPosition
END -- Τέλος loop
END
RETURN
END