تقسیم متن یا حباب ها به سطرها و جداول داده در SQL Server
به سطرها پیام دهید
به دنبال برخی دیگر از مقالات تابع SQL ، این تابعی است که ما به عنوان بخشی از کد برای جستجوی کتابخانه اسناد CMS خود استفاده می کنیم.
اول از همه ، تابع جدولی را اعلام می کند که بعداً بازگردانده می شود.
سپس هر محدود کننده تکراری را حذف می کنیم تا حجم سوابق بازگشتی را کاهش دهیم.
سپس ما رشته را حلقه می کنیم و کلمه را در جدول اعلام شده وارد می کنیم در هر اتفاقی که از مرز جدا می کنیم ، در ابتدا اعلام می کنیم.
این تابع از اتصال طرحواره استفاده می کند ، به طوری که می تواند در سایر توابع محدود شده به طرحواره ، که حتی می تواند به یک جدول متصل شود ، استفاده شود.
در SQL Server 2016 ، عملکرد داخلی STRING_SPLIT معرفی شد و تا زمانی که با نسخه 130 سازگاری دارید ، می توانید از آن برای یک جداکننده واحد استفاده کنید.
با پیشرفت کار ، ما از string_split پیشی گرفته ایم و اکنون می توانیم مقدماتی متن را مدیریت کنیم ، حتی وقتی فقط در برخی ستون ها وجود داشته باشد.
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 استفاده کنید
همچنین می توانید از این مورد استفاده شده برای خود برای تقسیم چند جداکننده یا با استفاده از PIVOT برای ایجاد جدول از مجموعه نتایج خود استفاده کنید. در زیر یک عملکرد دو محدود شده وجود دارد که به یک جدول تقسیم می شود.
با استفاده از این می توانید به سرعت میزان کاراکترهای ارسالی بین رایانه ها را کاهش دهید. در صورت نیاز می توانید از مرز جداکننده تا 10 حرف استفاده کنید.
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 |
