Funzione SPLIT in SQL?
Luca
Avrei necessità di avere in SQL una funzione tipo la Split del VB. Ho
trovato http://www.devx.com/tips/Tip/20009 Può essere valida?
In VB lo Split() ritorna un array e quindi posso fare una cosa del tipo
MioArray(5) per conoscere la stringa in 6° posizione. E' possibile una cosa
simile anche in SQL utilizzando il link sopra menzionato? Oppure avete
qualche idea migliore per eseguire lo split di una stringa?
Il mio problema è che ho una stringa tipo
"001|marco|18;012|silvia|15;024|giorgio|31;" e che dovrei fare una stored
procedure che riceva in input questa stringa, mi faccia lo split dei valori
per inserirmeli in una tabella:
Codice Nome Età
001 marco 18
012 silvia 15
024 giorgio 31
E' chiaro che potrei fare lo split nella mia applicazione ed inviare la
richiesta di 3 INSERT, ma vorrei fare tutto in una SP in modo da ottimizzare
le prestazioni (anche perché la strnga può essere molto lunga).
Grazie!
Luca
Mauro Servienti
con il prezioso aiuto di un amico (Filippo, www.jargoncode.it) abbiamo
realizzato la seguente funzione che fa esattamente uno 'split' e restituisce
una tabella con una sola colonna "gnsWord" contenente tutte le parole
estratte dalla frase di partenza
-----
CREATE FUNCTION gnsSplit ( @gnsText varchar(1000), @gnsSplitChar char(1) )
RETURNS @gnsSplittedTable TABLE ( gnsWord varchar(1000) )
AS
BEGIN
DECLARE @N int
DECLARE @lastCur int
DECLARE @strLen int
/*
@divLen serve nel caso il carattere per cui splittare non sia un singolo
carattere ma una stringa, per far si
che funzioni è inoltre necessario modificare il parametro in imput
@gnsSplitChar per accettare un varchar
di lunghezza diversa da 1
*/
DECLARE @divLen int
DECLARE @tmpStr varchar(255)
SET @lastCur = 1
SET @N = 1
SET @strLen = LEN( @gnsText )
SET @divLen = LEN( @gnsSplitChar )
WHILE @N <= @strLen
BEGIN
SET @tmpStr = SUBSTRING(@gnsText, @N, @divLen)
IF(@tmpStr = @gnsSplitChar)
BEGIN
INSERT INTO @gnsSplittedTable ( gnsWord ) VALUES ( SUBSTRING ( @gnsText,
@lastCur, @N - @lastCur ) )
SET @lastCur = @N + @divLen
SET @N = @N + @divLen - 1
END
SET @N = @N + 1
END
INSERT INTO @gnsSplittedTable ( gnsWord ) VALUES ( SUBSTRING ( @gnsText,
@lastCur, @N - @lastCur ) )
RETURN
END
-----
Spero faccia la caso tuo.
Ciao
--
mauro servienti
.
"Luca" <nos...@nospam.com> wrote in message
news:qOSac.1774$rM4....@news4.tin.it...
Luca
news:eAkNIy9F...@TK2MSFTNGP11.phx.gbl...
> Ciao,
>
> con il prezioso aiuto di un amico (Filippo, www.jargoncode.it) abbiamo
> realizzato la seguente funzione che fa esattamente uno 'split' e
restituisce
> una tabella con una sola colonna "gnsWord" contenente tutte le parole
> estratte dalla frase di partenza
Grazie Mauro,
ma dopo che ho la tabella, come faccio a ciclare tra i vari records per
effettuare l'INSERT di ognuno?
Grazie,
Luca
Mauro Servienti
del tipo
SELECT * INTO miaTabella FROM Split('stringa', 'parametro')
Quello che non avevo notato, scusa, è che dovresti splittare due volte una
per ';' e quindi per '|', perchè se non ho capito male i ';' delimitano le
"righe", mentre i '|' delimitano le colonne all'interno della riga...
quindi...
forse la cosa migliore è realizzare un'ulteriore funzione che in primis
splitti per ';' quindi ad ogni iterazione (WHILE) risplitta per '|' e
inserisce i dati ottenuti in una tabella con le colonne che ti interessano.
--
mauro servienti
.
"Luca" <nos...@nospam.com> wrote in message
news:pWTac.2461$rM4....@news4.tin.it...
Luca Bovo
una volta che hai la tabella che contiene
gnsword
001|marco|18
012|silvia|15
024|giorgio|31
--crei un cursore
DECLARE crs_word FOR SELECT gnsword from Tabella
--dichiari la variabile
DECLARE @gnsword varchar(1000)
OPEN crs_word
FETCH crs_word
INTO @gnsword
WHILE @@FETCH_STATUS = 0
BEGIN
--QUI UTILIZZI LA FUNZIONE DI SPLIT UTILIZZANDO COME
STRINGA DA RICERCARE IL PIPE (|), ED ALL'INTERNO DELLA
FUNZIONE FAI UNA INSERT NELLA TABELLA CHE TI INTERESSA
FETCH crs_word
INTO @gnsword
END
CLOSE crs_word
DEALLOCATE crs_word
CIAO
>-----Messaggio originale---
>.
>
Andrea Montanari
"Luca Bovo" <mr_...@email.it> ha scritto nel messaggio
news:13fe101c417e8$75261ca0$a601...@phx.gbl...
> Ciao,
> una volta che hai la tabella che contiene
>
> gnsword
> 001|marco|18
> 012|silvia|15
> 024|giorgio|31
>
> --crei un cursore
>
ed io ti picchio ... =;-)))
come Mauro ha riportato, meglio usare qualche cosa simile a
SET NOCOUNT ON
GO
CREATE FUNCTION dbo.Split(@dati VARCHAR(8000))
RETURNS @Result TABLE(Nome VARCHAR(10))
AS BEGIN
--solo esemplificativo
INSERT INTO @Result VALUES('Lorenzo')
INSERT INTO @Result VALUES('Luca')
INSERT INTO @Result VALUES('Gianluca')
INSERT INTO @Result VALUES('Andrea')
RETURN
END
GO
CREATE TABLE #Students (
Id INT NOT NULL IDENTITY ,
Nome VARCHAR(10) NOT NULL ,
altri_dati VARCHAR(1) NOT NULL DEFAULT 'x' ,
altri_dati1 INT NOT NULL DEFAULT 0
)
GO
INSERT INTO #Students (Nome) SELECT * FROM dbo.Split ('') ORDER BY Nome
SELECT * FROM #Students
GO
DROP TABLE #Students
DROP FUNCTION dbo.Split
--<-------------
Id Nome altri_dati altri_dati1
----------- ---------- ---------- -----------
1 Andrea x 0
2 Gianluca x 0
3 Lorenzo x 0
4 Luca x 0
saluti
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply
Luca Bovo
io invece non ti picchio :-)))
Ti faccio solo notare che la seconda domanda di Luca era:
"Grazie Mauro,
ma dopo che ho la tabella, come faccio a ciclare tra i
vari records per
effettuare l'INSERT di ognuno?
Grazie,
Luca"
Se Luca mi chiede come fare a ciclare su una tabella io
glielo spiego.... dopodichè se mi volevi fare notare che
il mio suggerimento NON era il migliore per risolvere il
quesito sulla funzione di split, siamo d'accordo.
Ciao
>-----Messaggio originale---
>.
>
Mauro Servienti
perchč la soluzione con i cursori č cosě deplorevole?, io non l'ho proposata
semplicemente perchč non so cosa sia :-)
Grazie,
--
mauro servienti
.
"Andrea Montanari" <andrea...@virgilio.it> wrote in message
news:c4h7h2$2jerk7$1...@ID-207518.news.uni-berlin.de...
Andrea Montanari
>Ciao,
>io invece non ti picchio :-)))
ed io continuo a picchiarti =;-))))
>Ti faccio solo notare che la seconda domanda di Luca era:
>.....
cio' non vuol dire che, se uno mi chiede: "come faccio a farmi saltare le
cervella con una Colt 45?" io gli riponda "tira il grilletto" =;-)))
la mia riposta sara' del tipo.... "guarda che ti fai del male, se continui"
=;-)
ed il paragone non e' superficiale...
l'uso dei cursori, se non necessario, e non sono molte le occasioni in cui
sia davvero necessario, sono una vera "disgrazia", con anche pesanti
conseguenze sul sistema.... SQL Server e' nato come server SET BASED, per
cui basato su set di risultati, e non per gestire cursori che vadano avanti
ed indietro riga per riga... ma queste cose le sai gia' senza bisgno che le
dica io...
questa situazione e' sicuramente risolvibile in maniera standard con set di
risultati.. =;-D
chiaramente ti picchio con affetto =;-D
Luca Bovo
probabilmente utilizzo i cursori per "comodità", o forse
per abitudine.
allora ti chiedo la procedura che riporto come la faresti
senza cursore.... anche se in realtà penso di sapere già
la risposta.
(Struttura è una tabella ricorsiva (l'alberatura ha almeno
5 livelli) in cui ogni idtree ha il suo padre: la
procedura deve ricercare sulla tabella gli
idtree "orfani".... gli idtree che hanno padre = 0 sono la
radice e quindi li escludo all'inizio. Gli idtree "orfani"
devono essere inseriti in STRUTTURA_ORFANI).
Grazie
DECLARE @int_idtree int
DECLARE @int_padre int
DECLARE crs_struttura CURSOR FOR SELECT idtree, padre FROM
STRUTTURA WHERE padre <> 0
OPEN crs_struttura
FETCH crs_struttura
INTO @int_idtree, @int_padre
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT * FROM STRUTTURA WHERE
idtree = @int_padre)
BEGIN
INSERT INTO
STRUTTURA_ORFANI SELECT * FROM STRUTTURA WHERE idtree =
@int_idtree
END
FETCH crs_struttura
INTO @int_idtree, @int_padre
END
CLOSE crs_struttura
DEALLOCATE crs_struttura
END
>-----Messaggio originale---
>.
>
giorgio rancati
>bene,
>probabilmente utilizzo i cursori per "comodità", o forse
>per abitudine.
>allora ti chiedo la procedura che riporto come la faresti
>senza cursore.... anche se in realtà penso di sapere già
>la risposta.
>(Struttura è una tabella ricorsiva (l'alberatura ha almeno
>5 livelli) in cui ogni idtree ha il suo padre: la
>procedura deve ricercare sulla tabella gli
>idtree "orfani".... gli idtree che hanno padre = 0 sono la
>radice e quindi li escludo all'inizio. Gli idtree "orfani"
>devono essere inseriti in STRUTTURA_ORFANI).
Ciao Luca,
scusa se mi intrometto, a me piacciono molto questi quesiti, ma forse non
sono riuscito a capire, ho provato con qualche dato e con una semplice query
di accodamento ottengo lo stesso risultato del tuo batch, evidentemente mi
sfugge qualcosa, puoi chiarire con un esempio ?
Ciao Giorgio
es...
---------------------------
USE TEMPDB
GO
CREATE TABLE STRUTTURA
(Idtree int ,
Padre int)
GO
CREATE TABLE STRUTTURA_ORFANI
(Idtree int ,
Padre int)
GO
INSERT STRUTTURA VALUES(1,0)
INSERT STRUTTURA VALUES(2,1)
INSERT STRUTTURA VALUES(3,1)
INSERT STRUTTURA VALUES(4,1)
INSERT STRUTTURA VALUES(5,2)
INSERT STRUTTURA VALUES(6,5)
INSERT STRUTTURA VALUES(7,99) --orfano
INSERT STRUTTURA VALUES(8,99) --orfano
------------------
INSERT INTO STRUTTURA_ORFANI
SELECT S1.*
FROM STRUTTURA S1
LEFT JOIN
STRUTTURA S2 on S1.Padre=S2.IdTree
WHERE S2.Idtree IS NULL
AND S1.Padre<>0
--------------------
SELECT * FROM STRUTTURA_ORFANI
DROP TABLE STRUTTURA,STRUTTURA_ORFANI
------------------------------------------------
risultato
Idtree Padre
----------- -----------
7 99
8 99
Andrea Montanari
"Mauro Servienti" <tech[remove]@[nospam]topics.it> ha scritto nel messaggio
news:%23XXUQAA...@TK2MSFTNGP12.phx.gbl...
> Scusate... ma la mia "profanaggine" è veramente tanta:
>
> perchè la soluzione con i cursori è così deplorevole?, io non l'ho
proposata
> semplicemente perchè non so cosa sia :-)
vorrei tentare di dare una riposta "filosofica" a te e parzialmente
un'indicazione anche a Luca, che mi sembra abbia purtroppo personalizzato le
mie precedenti affermazioni... mi scuso con lui se cio' lo avesse in qualche
modo offeso, in quanto non e' mia intenzione ne' sedere in cattedra ne'
arrogarmi pretese di deus ex machina.
Non voglio denigrare ne' criminalizzare in assoluto soluzioni basate sui
cursori, perche' essi costituiscono uno strumento potentissimo e
validissimo, in alcune circostanze, ma sicuramente non nella richiesta da te
portata avanti, dove la soluzione basata sul set di dati e' sicuramente piu'
pulita, elegante e performante.
Forse l'esempio riportato piu' sotto da Luca rientra in tali evenienze, non
l'ho pero'neanche guardato, perche' purtroppo richiederebbe un tempo che ora
non ho diponibile, visto che anche devo ancora preparare i bagagli =;-) ,
oltre a dover chiudere alcune altre cose prima di allontanarmi per un po'...
In generale un cursore e' un meccanismo messo in essere per valutare le
righe ritornate da un'operazione di SELECT a livello di singola riga, e riga
per riga, a livello di server, un po' come si faceva lato client nel mondo
ISAM dei file sequenziali, dove si apriva un file, lo si scorreva
dall'inizio fino ad eventualmente trovare il record desiderato per
modificarlo o meno... questo comporta chiaramente un investimento in risorse
anche molto pesante.
Al di la' della "storia" in se', che ricorda i tempi di collaborazione tra
Micorosft stessa con un colosso del mondo ISAM quale Ashton Tate, che ha
fortemente voluto tale implementazione fuori standard ANSI/ISO nel mondo dei
database relazionali, SQL Server mette a disposizione 4 tipo di cursori.
a seconda del tipo implementato, i dati ritornati in record, possono essere
a meno scrollabili e modificabili, oltre che a permettere la capacita' di
essere a conoscenza di modifiche avvenute al di fuori delle attivita' del
cursore in essere.
L'uso di cursori in se', e' tanto piu' valido, quanto esso si avvicina a
mondi di query dinamiche, operazioni riga per riga e presentazioni di dati
scrollabili, dove "operazioni riga per riga" sono costituite da complesse
operazioni multi-comando eseguibili riga per riga, e le query dinamiche
costruiscono sql dinamico modificabile riga per riga e basato magari su
altri risultati paralleli dal risultato principale... cicli dentro clicli..
non mi addentro nella sintassi in se' che comunque puoi trovare sui BOL...
Mauro Servienti
ho allargato ancora un po' le mie vedute
--
mauro servienti
.
"Andrea Montanari" <andrea...@virgilio.it> wrote in message
news:c4jrqn$2i0808$1...@ID-207518.news.uni-berlin.de...
> salve Mauro,
> "Mauro Servienti" <tech[remove]@[nospam]topics.it> ha scritto nel
messaggio
> news:%23XXUQAA...@TK2MSFTNGP12.phx.gbl...
> > Scusate... ma la mia "profanaggine" č veramente tanta:
> >
> > perchč la soluzione con i cursori č cosě deplorevole?, io non l'ho
> proposata
> > semplicemente perchč non so cosa sia :-)
Luca Bovo
Il mio era solo un tentativo (malriuscito) per evidenziare che NON tutte le
elaborazioni si possono ridurre a result set, o perlomeno NON tutte le
elaborazioni sono riducibili facilmente a result set.
Tutto qui.
L'esempio che ho portato è uno di quelli in cui si riesce a fare tutta
l'elaborazione con una semplice join.... ma non è sempre così...
Ciao
"giorgio rancati" <giorgio_No_Sp...@tiscali.it> wrote in message
news:%231iVrmB...@TK2MSFTNGP12.phx.gbl...
giorgio rancati
> Infatti la tua query č corretta.....
> Il mio era solo un tentativo (malriuscito) per evidenziare che NON tutte
le
> elaborazioni si possono ridurre a result set, o perlomeno NON tutte le
> elaborazioni sono riducibili facilmente a result set.
> Tutto qui.
> l'elaborazione con una semplice join.... ma non č sempre cosě...
Penso che su questo siano d'accordo anche i progettisti di Sql Server visto
che lo strumento cursori esiste e viene utilizzato anche in alcune Sp di
sistema. ;-)
Ciao Giorgio