T-SQL: opposto alla concatenazione di stringhe: come dividere la stringa in più record

Possibile duplicato:
Dividi la stringa in SQL

Ho visto un paio di domande relative alla concatenazione di stringhe in SQL. Mi chiedo come affronterebbe il problema opposto: suddividere la stringa delimitata da coma in righe di dati:

Diciamo che ho tavoli:

userTypedTags(userID,commaSeparatedTags) 'one entry per user tags(tagID,name) 

E voglio inserire i dati nella tabella

 userTag(userID,tagID) 'multiple entries per user 

Ispirato da quali tag non sono nel database? domanda

MODIFICARE

Grazie per le risposte, in realtà più che uno merita di essere accettato, ma posso sceglierne solo uno, e la soluzione presentata da Cade Roux con le ricorsioni mi sembra abbastanza pulita. Funziona su SQL Server 2005 e versioni successive.

Per la versione precedente di SQL Server è ansible utilizzare la soluzione fornita da miies . Per lavorare con il tipo di dati di testo, sarà utile la risposta wcm . Grazie ancora.

Ci sono una vasta gamma di soluzioni a questo problema documentato qui , incluso questo piccolo gioiello:

 CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT pn, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s FROM Pieces ) 

È inoltre ansible ottenere questo effetto utilizzando XML, come visto qui , che rimuove la limitazione delle risposte fornite che sembrano includere la ricorsione in qualche modo. L’uso particolare che ho fatto qui consente fino a un delimitatore di 32 caratteri, ma potrebbe essere aumentato per quanto grande debba essere.

 create FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX)) RETURNS TABLE AS RETURN ( SELECT r.value('.','VARCHAR(MAX)') as Item FROM (SELECT CONVERT(XML, N'' + REPLACE(REPLACE(REPLACE(@s,'& ','& '),'<','<'), @sep, '') + '') as valxml) x CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r) ) 

Quindi puoi invocarlo usando:

 SELECT * FROM dbo.Split(' ', 'I hate bunnies') 

Che restituisce:

 ----------- |I | |---------| |hate | |---------| |bunnies | ----------- 

Dovrei notare, in realtà non odio i coniglietti … mi è semplicemente venuto in mente per qualche motivo.


Quanto segue è la cosa più vicina che potrei ottenere usando lo stesso metodo in una funzione di valore inline della tabella. NON USARLO, È ORRIBILE INEFFICIENTE! È qui solo per amor di riferimento.

 CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX)) RETURNS TABLE AS RETURN ( SELECT r.value('.','VARCHAR(MAX)') as Item FROM (SELECT CONVERT(XML, N'' + REPLACE(@s, @sep, '') + '') as valxml) x CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r) ) 

Io uso questa funzione (SQL Server 2005 e versioni successive).

 create function [dbo].[Split] ( @string nvarchar(4000), @delimiter nvarchar(10) ) returns @table table ( [Value] nvarchar(4000) ) begin declare @nextString nvarchar(4000) declare @pos int, @nextPos int set @nextString = '' set @string = @string + @delimiter set @pos = charindex(@delimiter, @string) set @nextPos = 1 while (@pos <> 0) begin set @nextString = substring(@string, 1, @pos - 1) insert into @table ( [Value] ) values ( @nextString ) set @string = substring(@string, @pos + len(@delimiter), len(@string)) set @nextPos = @pos set @pos = charindex(@delimiter, @string) end return end 

Per il caso particolare di dividere le stringhe in parole mi sono imbattuto in un’altra soluzione per SQL Server 2008.

 with testTable AS ( SELECT 1 AS Id, N'how now brown cow' AS txt UNION ALL SELECT 2, N'she sells sea shells upon the sea shore' UNION ALL SELECT 3, N'red lorry yellow lorry' UNION ALL SELECT 4, N'the quick brown fox jumped over the lazy dog' ) SELECT display_term, COUNT(*) As Cnt FROM testTable CROSS APPLY sys.dm_fts_parser('"' + txt + '"', 1033, 0,0) GROUP BY display_term HAVING COUNT(*) > 1 ORDER BY Cnt DESC 

ritorna

 display_term Cnt ------------------------------ ----------- the 3 brown 2 lorry 2 sea 2 

Leggera modifica della soluzione di cui sopra in modo che funzioni con i delimitatori a lunghezza variabile.

 create FUNCTION dbo.fn_Split2 (@sep nvarchar(10), @s nvarchar(4000)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2)) FROM Pieces WHERE stop > 0 ) SELECT pn, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s FROM Pieces ) 

NB: Ho usato datalength () poiché len () riporta erroneamente se ci sono spazi finali.

Ecco una funzione Split compatibile con le versioni di SQL Server precedenti al 2005.

 CREATE FUNCTION dbo.Split(@data nvarchar(4000), @delimiter nvarchar(100)) RETURNS @result table (Id int identity(1,1), Data nvarchar(4000)) AS BEGIN DECLARE @pos INT DECLARE @start INT DECLARE @len INT DECLARE @end INT SET @len = LEN('.' + @delimiter + '.') - 2 SET @end = LEN(@data) + 1 SET @start = 1 SET @pos = 0 WHILE (@pos < @end) BEGIN SET @pos = CHARINDEX(@delimiter, @data, @start) IF (@pos = 0) SET @pos = @end INSERT @result (data) SELECT SUBSTRING(@data, @start, @pos - @start) SET @start = @pos + @len END RETURN END 

Usando CLR, ecco un’alternativa molto più semplice che funziona in tutti i casi, tuttavia il 40% più veloce della risposta accettata:

 using System; using System.Collections; using System.Data.SqlTypes; using System.Text.RegularExpressions; using Microsoft.SqlServer.Server; public class UDF { [SqlFunction(FillRowMethodName="FillRow")] public static IEnumerable RegexSplit(SqlString s, SqlString delimiter) { return Regex.Split(s.Value, delimiter.Value); } public static void FillRow(object row, out SqlString str) { str = new SqlString((string) row); } } 

Ovviamente, è ancora 8 volte più lento di regexp_split_to_table di PostgreSQL.

 SELECT substring(commaSeparatedTags,0,charindex(',',commaSeparatedTags)) 

ti darò il primo tag. È ansible procedere in modo simile per ottenere il secondo e così via combinando sottostringa e charindex un livello più profondo ogni volta. Questa è una soluzione immediata, ma funziona solo con pochissimi tag in quanto la query cresce molto rapidamente in termini di dimensioni e diventa illeggibile. Passa poi alle funzioni, come delineato in altre risposte più sofisticate a questo post.

Ho scritto questo tempo indietro. Presume che il delimitatore è una virgola e che i singoli valori non superano 127 caratteri. Potrebbe essere modificato abbastanza facilmente.

Ha il vantaggio di non essere limitato a 4000 caratteri.

In bocca al lupo!

 ALTER Function [dbo].[SplitStr] ( @txt text ) Returns @tmp Table ( value varchar(127) ) as BEGIN declare @str varchar(8000) , @Beg int , @last int , @size int set @size=datalength(@txt) set @Beg=1 set @str=substring(@txt,@Beg,8000) IF len(@str)<8000 set @[email protected] ELSE BEGIN set @last=charindex(',', reverse(@str)) set @str=substring(@txt,@Beg,[email protected]) set @[email protected][email protected]+1 END declare @workingString varchar(25) , @stringindex int while @Beg<[email protected] Begin WHILE LEN(@str) > 0 BEGIN SELECT @StringIndex = CHARINDEX(',', @str) SELECT @workingString = CASE WHEN @StringIndex > 0 THEN SUBSTRING(@str, 1, @StringIndex-1) ELSE @str END INSERT INTO @tmp(value) VALUES (cast(rtrim(ltrim(@workingString)) as varchar(127))) SELECT @str = CASE WHEN CHARINDEX(',', @str) > 0 THEN SUBSTRING(@str, @StringIndex+1, LEN(@str)) ELSE '' END END set @str=substring(@txt,@Beg,8000) if @[email protected] set @[email protected]+1 else IF len(@str)<8000 set @[email protected] ELSE BEGIN set @last=charindex(',', reverse(@str)) set @str=substring(@txt,@Beg,[email protected]) set @[email protected][email protected]+1 END END return END 

Ho risposto alla domanda “Nathan Wheeler” perché ho trovato che la risposta “Cade Roux” non funzionava oltre una determinata dimensione di stringa.

Paio di punti

-Ho trovato che aggiungere la parola chiave DISTINCT ha migliorato le prestazioni per me.

-La risposta di Nahan funziona solo se i tuoi identificatori sono di 5 caratteri o meno, ovviamente puoi aggiustarlo … Se gli elementi che stai dividendo sono identificatori INT come sono io puoi farci come me di seguito:

 CREATE FUNCTION [dbo].Split ( @sep VARCHAR(32), @s VARCHAR(MAX) ) RETURNS @result TABLE ( Id INT NULL ) AS BEGIN DECLARE @xml XML SET @XML = N'' + REPLACE(@s, @sep, '') + '' INSERT INTO @result(Id) SELECT DISTINCT r.value('.','int') as Item FROM @xml.nodes('//root//r') AS RECORDS(r) RETURN END 

Solitamente lo faccio con il seguente codice:

 create function [dbo].[Split](@string varchar(max), @separator varchar(10)) returns @splited table ( stringPart varchar(max) ) with execute as caller as begin declare @stringPart varchar(max); set @stringPart = ''; while charindex(@separator, @string) > 0 begin set @stringPart = substring(@string, 0, charindex(@separator, @string)); insert into @splited (stringPart) values (@stringPart); set @string = substring(@string, charindex(@separator, @string) + len(@separator), len(@string) + 1); end return; end go 

Puoi testarlo con questa query:

 declare @example varchar(max); set @example = 'one;string;to;rule;them;all;;'; select * from [dbo].[Split](@example, ';');