تقسيم النص أو النقاط الكبيرة إلى صفوف وجداول بيانات في 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 ، لإنشاء جدول من مجموعة النتائج الخاصة بك. يوجد أدناه وظيفة محددة مزدوجة ، مقسمة إلى جدول.
باستخدام هذا ، يمكنك تقليل عدد الأحرف المرسلة بين أجهزة الكمبيوتر بسرعة. إذا لزم الأمر ، يمكنك استخدام محدد يصل طوله إلى 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 |
