SQL Server'da metin veya blobları veri satırlarına ve tablolara bölme
Satırlara Metin
Diğer SQL işlevi makalelerimden sonra, bu, CMS belge kitaplığımızı aramak için kodun bir parçası olarak kullandığımız bir işlevdir.
Her şeyden önce, işlev daha sonra döndürülecek bir tablo bildirir.
Ardından, döndürülen kayıtların hacmini azaltmak için yinelenen sınırlayıcıları çıkarırız.
Daha sonra dizgede dolaşırız ve başlangıçta bildirdiğimiz sınırlayıcının her oluşumunda sözcüğü bildirilen tabloya ekleriz.
Bu işlev şema bağlamayı kullanır, böylece bir tabloya bile bağlanabilen diğer şemaya bağlı işlevlerde kullanılabilir.
SQL Server 2016'da yerleşik STRING_SPLIT işlevi tanıtıldı ve 130 sürümünün üzerinde uyumluluğunuz olduğu sürece bunu tek bir sınırlayıcı için kullanabilirsiniz.
Çalışma ilerledikçe, string_split'i aştık ve artık yalnızca belirli sütunlarda mevcut olsa bile metin niteleyicileri işleyebiliriz.
SQL Code - Basic Function
CREATE FUNCTION [dbo].[TextToRows](@Delim NVARCHAR(10),@Value NVARCHAR(MAX))--Delimeter and Search StringRETURNS @Table TABLE(WordInt BIGINT IDENTITY(1,1) PRIMARY KEY,WordStr NVARCHAR(MAX)) WITH SCHEMABINDING --Return TableAS BEGINSET @Value=LTRIM(RTRIM(@Value))--Trim forward/trailing spacesWHILE (CHARINDEX(@Delim+@Delim,@Value,1)<>0) BEGINSET @Value=REPLACE(@Value,@Delim+@Delim,@Delim)--Remove double delims (if required)...ENDDECLARE @CurPos BIGINTSET @CurPos=0DECLARE @NextPos BIGINTSET @NextPos=CHARINDEX(@Delim,@Value,@CurPos+1)WHILE @NextPos>0 BEGININSERT INTO @Table(WordStr)SELECT REPLACE(SUBSTRING(@Value,@CurPos,(@NextPos-@CurPos)),@Delim,'')--Add first word if existsSET @CurPos=@NextPosSET @NextPos=CHARINDEX(@Delim,@Value,@CurPos+1)ENDINSERT INTO @Table(WordStr) SELECT REPLACE(SUBSTRING(@Value,@CurPos,LEN(@Value)),@Delim,'')--Add last word (or whole word)RETURNENDGOSELECT * FROM TextToRows(',','Gavin,Clayton,Test,Data')Result
Clayton
Test
Data
New SQL Code - With Text Qualifiers
CREATE FUNCTION dbo.[TextToRowsText](@Delim NVARCHAR(10),@Value NVARCHAR(MAX),@Text NVARCHAR(1))--Delimeter and Search StringRETURNS @Table TABLE(WordInt BIGINT IDENTITY(1,1) PRIMARY KEY,WordStr NVARCHAR(MAX)) --Return TableAS BEGINDECLARE @TextOn INT=(CASE WHEN LEFT(@Value,1)=@Text AND LEN(@Text)>0 THEN 1 ELSE 0 END)DECLARE @NextPos BIGINT=CHARINDEX((CASE WHEN @TextOn=1 THEN @Text+@Delim ELSE @Delim END),@Value,LEN(@Delim)+(@TextOn))WHILE @NextPos>0 BEGININSERT INTO @Table(WordStr) SELECT SUBSTRING(@Value,LEN(@Text)+@TextOn,(@NextPos-(LEN(@Text)+@TextOn)))SET @Value=SUBSTRING(@Value,@NextPos+@TextOn+LEN(@Delim),9999999)SET @TextOn=(CASE WHEN LEFT(@Value,1)=@Text AND LEN(@Text)>0 THEN 1 ELSE 0 END)SET @NextPos=CHARINDEX((CASE WHEN @TextOn=1 THEN @Text+@Delim ELSE @Delim END),@Value,LEN(@Text)+@TextOn)ENDSET @TextOn=(CASE WHEN LEFT(@Value,1)=@Text AND LEN(@Text)>0 THEN 1 ELSE 0 END)IF LEN(@Value)>0 INSERT INTO @Table(WordStr) SELECT SUBSTRING(@Value,LEN(@Text)+@TextOn,(CASE WHEN RIGHT(@Value,1)=@Text THEN LEN(@Value)-(LEN(@Text)+@TextOn) ELSE 9999999 END))RETURNENDGOSELECT * FROM dbo.TextToRowsText(',','Gavin,"Clayton","Test",Data','"')PIVOT ile kullanın
Ayrıca, birden çok sınırlayıcıyı bölmek için kendisine uygulanan bunu veya sonuç kümenizden bir tablo oluşturmak için bir PIVOT ile kullanabilirsiniz. Aşağıda, tekrar bir tabloya bölünmüş, çift sınırlandırılmış bir fonksiyon bulunmaktadır.
Bunu kullanarak bilgisayarlar arasında gönderilen karakter miktarını hızla azaltabilirsiniz. Gerekirse, en fazla 10 karakter uzunluğunda bir sınırlayıcı kullanabilirsiniz.
Double Delimited & Pivot
DECLARE @Str NVARCHAR(1000)='1;1.2;1.2.3;1.2.3.4'SELECT * FROM (SELECT ttr.WordStr Orig,ttr2.WordInt,ttr2.WordStrFROM dbo.TextToRows(';',@Str) ttrOUTER APPLY dbo.TextToRows('.',ttr.WordStr) ttr2) ttrdPIVOT (MAX(WordStr) FOR WordInt IN ([1],[2],[3],[4])) PivDouble Delimited Pivot Result
| Orig | 1 | 2 | 3 | 4 |
| 1 | 1 | NULL | NULL | NULL |
| 1.2 | 1 | 2 | NULL | NULL |
| 1.2.3 | 1 | 2 | 3 | NULL |
| 1.2.3.4 | 1 | 2 | 3 | 4 |
