هشتمین چالش SQL (اصلاح رشته)

[محمد سلیم آبادی]

توضیح مساله:
یکسری سطر داریم که هر کدام حاوی یک رشته می باشند. این رشته ها حاوی یکسری کلمات هستند که با space از همدیگر جدا شده اند. در این رشته کاراکتر های ناخواسته هستند که بایستی حذف شوند، هر کاراکتری که خارج از رنج a-z باشد باید ابتدا از رشته حذف شود. بعد از این مرحله اگر بین دو کلمه بیش از یک کاراکتر space بود باید همه به غیر از یکی حذف شوند. در مرحله بعد کلمات باید بصورت مرتب شده (صعودی) در رشته چیده شوند و در انتها کلمات تکرار از رشته حذف شوند.


به طور نمونه اگر رشته ی زیر را داشته باشیم:

string: ha~s@an $# re@#za @#! jav@#ad ali mah@#di 7% ali bahman #)(&^ reza bah^&%ador hasan

باید تغییرات زیر روی رشته فوق اعمال شود:

step 1: hasan reza javad ali mahdi ali bahman reza bahador hasan

step 2: ali ali bahador bahman hasan hasan javad mahdi reza reza

step 3: ali bahador bahman hasan javad mahdi reza

create table test_string

(id int primary key not null,

string_value varchar(500) not null);



insert into test_string

values (1, 'ha~s@an      $#    re@#za         @#!       jav@#ad       ali   mah@#di      7%  ali bahman      #)(&^  reza bah^&%ador hasan');

مساله فوق تماما باید توسط یک کوئری (که میتونه حاوی چندین select تو درتو یا cte باشد) حل شود.

[cherchil_hra]

totalChar : جدا کردن حروف و حذف موارد خارج از محدوده و جایگزین کردن آنها با مقدار null
ستون های contour : شمارش کاراکترها / [group] : دسته بندی حروف براساس space بین آنها

TotalString: چسباندن حروف بر اساس گروهبندی روی ستون [group] و حذف کاراکترهای null و در نهایت حذف رشته های تکراری.
ltrim هم برای حذف حالت xml استفاده شده

MAXRECURSION 500: برای حالت برگشتی (خواندن کاراکتر به کاراکتر) که حداکثر برابر با طول @s خواهد بود.
نتیجه کار هم در رشته @result قرار داده می شود. (که از xml path هم میشه استفاده کرد)

DECLARE @s VARCHAR(500) = 

        '  ha~s@an $# re@#za @#! jav@#ad ali mah@#di 7% ali bahman #)(&^ reza bah^&%ador hasan ha~s@an';        

DECLARE @result AS VARCHAR(500) = '';



WITH 

totalChar AS

(

    SELECT SUBSTRING(@s, 1, 1)  AS c, 1 AS contour, 1 AS [group] 

    UNION ALL    

    SELECT CASE 

                WHEN SUBSTRING(@s, TotalChar.contour + 1, 1) LIKE('[a-z ]') THEN 

                     SUBSTRING(@s, TotalChar.contour + 1, 1)

                ELSE NULL

           END                    AS c,

           TotalChar.contour + 1  AS [COUNT],

           CASE 

                WHEN c = ' ' THEN [group] + 1

                ELSE [group]

           END

    FROM   TotalChar

    WHERE  LEN(@s) >= TotalChar.contour

) 

,TotalString AS (

                   SELECT DISTINCT(

                              SELECT LTRIM(b.c)

                              FROM   totalChar AS b

                              WHERE  a.[group] = b.[group]

                                     AND NOT(b.c IS NULL) 

                                         FOR XML PATH('')

                          ) AS [NAME]

                   FROM   totalChar AS a

                   WHERE  NOT(c IS NULL)

                  GROUP BY              a.[group]                                             

                )





SELECT @result=@result+[name]+' '

FROM   TotalString     

ORDER BY [NAME]

OPTION(MAXRECURSION 500)



   

SELECT ltrim(rtrim(@result))

[محمد سلیم آبادی]

ممنون از پاسختون.
دیدگاه recursive هم برای خودش شاهکاریه! گرچه من ازش خیلی غافل بودم. پروسه ای که من طی کردم تا به جواب برسه چندین برابر پیچیده تر از روش recursive شماست. فقط در پنج جای مختلف از توابع OLAP استفاده شده. ظاهرا سرعتش هم خیلی پایینه. باید به فکر خلق یک راه حل جدید باشم.

فقط لطف کنید کوئریتون رو به شکلی تغییر بدین که بر اساس داده های یک جدول اجرا بشه نه یک متغیر رشته ای. (گرچه میشه کدتون رو در قالب یک تابع در نظر گرفت و در کوئری ازش استفاده نمود ولی مد نظرم این بود که همه در قالب فقط یک کوئری باشن و از تابع و ... استفاده نشه)
برای این منظور از همون جدولی که در پست اول قرار داده شده استفاده کنید. این ده سطر هم در جدول درج کنید تا سرعت اجرای کوئریتون رو ببینید.

insert into test_string (id, string_value)

select 1,'ha~s@an      $#    re@#za         @#!       jav@#ad       ali   mah@#di      7%  ali bahman      #)(&^  reza bah^&%ador hasan'union all 

select 2,'         moh#$&*&()*$sen    (#^$&Y     javad jafar              mahdi        reza akbar         akbar akbar        'union all 

select 3,' hamed        mohsen         javad jafar         mahdi        reza akbar         akbar akbar        'union all 

select 4,'mamad mohsen                 mohsen         javad   )(&FSD#(*^$ jafar        maryah abozar mehdad'union all 

select 5,'zeynab mamad    reza akbar      @#@#@   akbar akbar     mohsen m@#@aryah abozar meh#$#dad'union all 

select 6,'mamad    reza akbar      @#@#@ zey#$#$#nab mamad    reza akbar      @#@#@   akbar akbar     mohsen m@#@aryah abozar meh#$#dad'union all 

select 7,'(#*^$*$%^@#*)$^#@$@%# reza (*%#^$(*#%$# reza )(*&#$%#)*&^)* reza #_(*&$#$r_(*&_(*&e(*&)(*&z_(*&_(*a ali allah'union all 

select 8,'*)&%^*)(&^           )*&^)*&^  mahdi mamad masoud mahyar _*&#$(*#$&#($*#&$#($*#&$(#*$'union all 

select 9,'                       *&^^$*$@#@*&#^@#(#(*a(*&#_($*&@#$_&#@l&_(*&(_*&l_(  *&_(*&a_*&_(*&*&h                      ali'union all 

select 10,'mahdi mahdi mahdi mohsen mohsen mohse hosein hosein hosein ali ali ali akbar akbar akbar';

یک اشکال خیلی کوچولویی که کدتون داره اینه که اولین کاراکتر رشته رو بررسی نمیکنه. مثلا اگه رشته با کاراکتر ^ شروع شده باشد در خروجی هم خواهد آمد.
یعنی این کد باید اصلاح بشه:

SELECT SUBSTRING(@s, 1, 1)  AS c, 1 AS contour, 1 AS [group] 

[محمد سلیم آبادی]

اولین تلاشم برای حل مساله:

WITH c1 AS

(

SELECT id, 

       chrs, 

       ROW_NUMBER() OVER(PARTITION BY ID ORDER BY rn) rnk, 

       CASE WHEN chrs <> '' THEN 1 ELSE 0 END c

FROM test_string s

CROSS APPLY (SELECT ROW_NUMBER() OVER(PARTITION BY chrs ORDER BY chrs)-rn rnk, chrs, rn

              FROM 

                   (SELECT SUBSTRING(string_value, n, 1)AS chrs,

                           ROW_NUMBER() OVER(ORDER BY n) rn

                    FROM number_table 

                    WHERE n <= LEN(string_value) 

                    AND PATINDEX('[ a-z]',SUBSTRING(string_value, n, 1)) = 1

                    )c

             )d

),c2 AS

(

SELECT *, MIN(rnk)OVER(PARTITION BY id,rk) rn

FROM (

        SELECT *, rnk - ROW_NUMBER() OVER(PARTITION BY id ORDER BY rnk) rk 

        FROM c1 

        WHERE c = 1

      )d

),c3 AS

(

SELECT DENSE_RANK() OVER(PARTITION BY id ORDER BY rn DESC) grping, *

FROM c2 

),c4 AS

(

SELECT id, word

FROM (SELECT id, grping FROM c3 GROUP BY id, grping)d

CROSS APPLY (SELECT ''+chrs

             FROM c3

             WHERE c3.id = d.id AND c3.grping = d.grping

             ORDER BY rnk 

             FOR XML PATH(''))c(word)

GROUP BY id, word

)

SELECT id, string

FROM (SELECT id FROM c4 GROUP BY id) d

CROSS APPLY (SELECT word+' '

             FROM c4

             WHERE c4.id = d.id 

             ORDER BY word

             FOR XML PATH(''))c(string);

کوئری بر اساس جدول اعداد هست (table_number). توضیحش هم خیلی مفصل هست. اگه مایل بودین جزء به جزء و قدم به قدم کوئری را شرح خواهم داد.

[محمد سلیم آبادی]

دومین تلاش، بر اساس دو کوئری بازگشتی با سرعت اجرای برق آسا.
PreCTE و Formating: حذف کاراکترهای ناخواسته به ازای هر سطر از جدول
CTE: تجزیه کردن رشته به کلمات تشکیل دهنده آن
و SELECT آخر: حذف کلمات تکراری، مرتب کردن کلمات و نهایتا الحاق آنها با FOR XML PATH

MAXRECURSION 0 نیز برای عدم بررسی تعداد اجرای کوئری بازگشتی

WITH PreCTE AS

(

    SELECT  1 cnt,

            id,

            string_value v,

            CAST('' AS VARCHAR(500)) total

     FROM test_string

     

     UNION ALL

 

     SELECT cnt +1,

            id,

            v,

            CAST(total + CASE WHEN SUBSTRING(v,cnt,1) LIKE '[ a-z]' 

                              THEN SUBSTRING(v,cnt,1)

                              ELSE '' 

                         END AS VARCHAR(500))

    FROM PreCTE

    WHERE cnt <= LEN(v)

),

  Formating AS

(

    SELECT id, MAX(total) total

    FROM PreCTE

    GROUP BY id

), 

 CTE AS

(

    SELECT id,

           CAST(' '+total+' ' AS VARCHAR(500)) total,

           CHARINDEX(' ', ' '+total) AS ix,

           CAST('' AS VARCHAR(500)) st

    FROM formating       

 

    UNION ALL

 

    SELECT id,

           total,

           CHARINDEX(' ', total, ix+1),

           SUBSTRING(total, ix+1, CHARINDEX(' ', total, ix+1)-ix)

    FROM CTE

    WHERE CHARINDEX(' ',total, ix+1) > 0

)

SELECT *

FROM (SELECT DISTINCT id FROM CTE)d

CROSS APPLY

(

    SELECT ''+st 

    FROM CTE

    WHERE cte.id=d.id

    GROUP BY st

    ORDER BY st

    FOR XML PATH('')

)c(list)

OPTION(MAXRECURSION 0);

[cherchil_hra]

با توجه به کوئری شما:

Modified: رشته تصحیح شده
[element]: هنگام بررسی رشته، بر اساس space کلمات تصحیح شده (از رشته Modified) از هم جدا می شوند و در این ستون قرار می گیرند.
[StartPoint]: نقطه شروع جدا سازی از رشته ی Modified. هنگامی مقدارش تغییر می کند که به space برسد.
[EndPoint] : نقطه پایانی برای انتخاب کلمه از رشته ی Modified. زمانی به مقدار آن اقزوده می شود که به کاراکتر مجاز رسیده باشد.

استفاده از LEN(MyString) + 1 برای بدست آوردن آخرین کلمه از رشته Modified

WITH CTE AS

(

    SELECT id,

           string_value              AS MyString,

           1                         AS Countour,

           CAST('' AS VARCHAR(500))  AS Modified,

           1                         AS [StartPoint],

           1                         AS [EndPoint],

           CAST('' AS VARCHAR(500))  AS [element]

    FROM   test_string

    

    UNION ALL

    

    SELECT id,

           MyString,

           Countour + 1,

           CAST(

               Modified + CASE 

                            WHEN SUBSTRING(MyString, Countour, 1) LIKE '[a-z ]' THEN 

                                 SUBSTRING(MyString, Countour, 1)

                            ELSE ''

                       END AS VARCHAR(500)

           )    AS Modified,

           CASE 

                WHEN SUBSTRING(MyString, Countour, 1) = ' ' THEN [EndPoint] + 1

                ELSE [StartPoint]

           END  AS [StartPoint],

           CASE 

                WHEN SUBSTRING(MyString, Countour, 1) LIKE '[a-z ]' THEN 

                     [EndPoint] + 1

                ELSE [EndPoint]

           END  AS [EndPoint],

           CAST(

               CASE 

                    WHEN SUBSTRING(MyString, Countour, 1) = ' ' THEN 

                    		LTRIM(RTRIM(SUBSTRING(Modified, [StartPoint], [EndPoint] -[StartPoint])))

                    ELSE NULL

               END AS VARCHAR(500)

           )    AS Element

    FROM   CTE

    WHERE  Countour <= LEN(MyString) + 1

)

 



SELECT id,

       (

           SELECT LTRIM(b.element + ' ')

           FROM   CTE AS b

           WHERE  a.id = b.id

           GROUP BY b.ELEMENT

           FOR XML PATH('')

       )    

FROM   CTE AS a

GROUP BY id

OPTION(MAXRECURSION 0)

[محمد سلیم آبادی]

قشنگ مساله رو حل میکنید.
من یک تغییر کوچیکی در کوئری دومم (بازگشتی) دادم. در کوئری اول موجود در PreCTE. کد به این شکل تغییر کرده است:

    SELECT  1 cnt,

            id,

            LTRIM(RTRIM(REPLACE(

            REPLACE(REPLACE(string_value, ' ', ' ▄'), '▄ ', ''), '▄', '')

                   )) v,

            CAST('' AS VARCHAR(500)) total

     FROM test_string

در واقع اومدم از یک تکنیک خاصی برای تبدیل چندین space به یکی بین کلمات استفاده کردم البته تکنیک شناخته شده ای هست یعنی من اولین نفری نیستم که اونو پیدا کرده. با همین فیلتر ساده تونستم سرعت کوئری رو افزایش بدم.

هر دو آخرین کوئری که ارائه داده شده اند رو با نرم افزار SQL Server Profiler مقایسه کردم. که نتیجش به شرح زیر است(مقایسه رو تنها 10 سطر):

-------------------------------

Method            Reads    CPU

-------------------------------

msalim #2         14539    47

cherchil_hra#2    18937    78

-------------------------------

  

[cherchil_hra]

ممنون!

کوئری شما و خودم رو روی 500 رکورد تست کردم.

زمان صرف شده:
کوئری من : 19 ثانیه / کوئری شما:13 ثانیه
در کوئری من cte با سرعت جواب میداد ولی موقع یکی کردن ستون ها زمانبر میشد. حتی از کد شما که رشته ها رو بهم وصل می کردید استفاده کردم ولی تفاوتی نکرد.

اینجا یک چیز برام جالب هست و نامشخص:

کوئری ها رو بر روی 2000 رکورد مشابه، به این صورت استفاده کردم:

کوئری برای شما:

SELECT id,st FROM cte

GROUP BY id,st

برای خودم:

 SELECT id,element

         FROM   cte

         WHERE NOT(element IS NULL) OR element<>''

         GROUP BY

                id,

                element

تعداد رکورد بازگشتی برای هردو 53279 عدد بود (باتوجه به ورودی های داده شده)

اما نکته جالب اینجاست:
کوئری من 13 ثانیه طول کشید ولی کوئری شما 23 ثانیه.

من علتش رو نفهمیدم، که چرا در نهایت وقتی از این مقادیر برای اتصال رشته ها استفاده می کنیم، کوئری من زمانبر تر می شود؟

[محمد سلیم آبادی]

این تیکه کد مربوط به آخرین قسمت کوئریتون هست،

SELECT id,

       (

           SELECT LTRIM(b.element + ' ')

           FROM   CTE AS b

           WHERE  a.id = b.id

           GROUP BY b.ELEMENT

           FOR XML PATH('')

       )    

FROM   CTE AS a

GROUP BY id

OPTION(MAXRECURSION 0)

به جاش این کد رو استفاده کنید و شاهد تغییر عظیم باشید:

SELECT id,

       (

           SELECT LTRIM(b.element + ' ')

           FROM   CTE AS b

           WHERE  a.id = b.id

           GROUP BY b.ELEMENT

           FOR XML PATH('')

       )   

FROM   test_string AS a

OPTION(MAXRECURSION 0)

در واقع موقعی که میشود id ها را به راحتی از جدول test_string استخراج نمود چرا باید متوسل به جدول CTE شد؟!

این اشتباه فاحش در کوئری من هم اتفاقا رخ داده بود (البته انتظار نمیره که نسخه اولیه کوئری کامل و دقیق و بدون اشتباه باشه. بایستی کوئری رو اصطلاحا پخت)
پخته شده ی کوئری من در ادامه قرار داده شده است (چند اصلاح مختلف). فقط قبلش اجازه بدین مقایسه ای داشته باشیم با کوئری جدیدم، کوئری سابق شما و نسخه اصلاح شده آن:

/*

Method----------------READS----------CPU---------DURATION

msalim                7445           16          29

cherchil_hra(befor)   18937          62          57

cherchil_hra(after)   10555          31          34

*/

سرعت اجرای این نسخه های جدید رو روی اون 500 رکورد امتحان کنید و ببینید چند ثانیه زمان کاهش یافته فقط به خاطر تغییر Cte بله test_string!

کوئری اصلاح شده من:

WITH PreCTE AS

(

    SELECT  1 cnt,

            id,

            REPLACE(REPLACE(REPLACE(REPLACE(string_value, ' ', ' ▄'), '▄ ', ''), '▄', '')

                   ,CHAR(1), '') v,

            CAST('' AS VARCHAR(500)) total

     FROM test_string

     

     UNION ALL

 

     SELECT cnt +1,

            id,

            v,

            CAST(total + CASE WHEN SUBSTRING(v,cnt,1) LIKE '[ a-z]' 

                              THEN SUBSTRING(v,cnt,1)

                              ELSE '' 

                         END AS VARCHAR(500))

    FROM PreCTE

    WHERE cnt <= LEN(v)

),

  Formating AS

(

    SELECT id, MAX(total) total

    FROM PreCTE

    GROUP BY id

), 



 CTE(ID, S, St) AS

(

    SELECT Id,

           CAST(total + ' ' AS VARCHAR(500)) ,

           CAST('' AS VARCHAR(500))

    FROM   Formating

    

    UNION ALL

 

    SELECT Id,

           SUBSTRING(S, CHARINDEX(' ', S) + 1, LEN(S) - CHARINDEX(' ', S) + 1),

           SUBSTRING(S, 1, CHARINDEX(' ', S))

    FROM CTE

    WHERE CHARINDEX(' ', S) > 0

)

SELECT d.id, c.list

FROM test_string d

CROSS APPLY

(

    SELECT LTRIM(''+st)

    FROM CTE

    WHERE cte.id=d.id

    GROUP BY st

    ORDER BY st

    FOR XML PATH('')

)c(list)

OPTION(MAXRECURSION 0);

[cherchil_hra]

ممنون از راهنماییتون! ولی در آخر هم متوجه نشدم چرا این کوئری انقدر طول میکشه. احتمال میدم به خاطر تعداد رکوردهایی باشه که اون اولش حساب کتاب می کنه...

ولی خوب، یک راه دیگه به ذهنم رسید و ترکیب اون با مطالب گفته شده چیزه خوبی شد، در این حد که روی 500 ردیف طی 3 ثانیه (رکوردهای من) و روی 1000 ردیف طی 11 ثانیه به جواب می رسیم:

ابتدا تمام حروف زائد رو با این روش حذف می کنیم:
1. اگه جزء حروف ما بود خود رشته برمی گرده در غیر این صورت کاراکتر '▄' با استفاده از Replace در تمام رشته جایگزینش می شود.
2. به جای اینکه به Countour یکی یکی اضافه بشه با استفاده از PATINDEX جای اولین کاراکتر نامجاز مشخص میشه و Countour برابر با این مقدار میشه در غیر این صورت Countour=1000 میشه یعنی کاراکتر نامجاز نداریم و نیازی نیست بقیه رشته رو بگردیم. البته چون داریم از varchar 500 استفاده می کنیم می تونیم برابر 501 قرارش بدیم.

در قسمت split اول کاراکتر '▄' حذف میشه و بعد طبق روش شما فاصله خالی های اضافه حذف میشه که 2 - 3 ثانیه زمان کوئری رو کم می کنه. اینجا رشته رو به کلماتش تقسیم می کنیم

در آخر هم اتصال!

USE test;



DECLARE @top INT = 500





;WITH CTE (ID,MyString,Countour) AS

(

    SELECT TOP(@top) 

		   id,

           string_value ,

           1                    

    FROM   test_string

    

    UNION ALL

    

    SELECT id,

           CAST(

               CASE 

                    WHEN SUBSTRING(MyString, Countour, 1) LIKE '[a-z ▄]' THEN MyString

                    ELSE REPLACE(MyString, SUBSTRING(MyString, Countour, 1), '▄')

               END AS VARCHAR(500)

           )

           MyString,

           CASE 

                WHEN PATINDEX('%[^a-z ▄]%', mystring) = 0 THEN 1000

                ELSE PATINDEX('%[^a-z ▄]%', mystring)

           END

    FROM   CTE

    WHERE  Countour <= LEN(MyString) + 1

)





, SPLIT(ID, string, word,StartPoint) AS

(

        

    SELECT id,

            CAST( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(mystring, '▄',''), ' ', ' ▄'), '▄ ', ''), '▄', ''),CHAR(1), '') + ' ' AS VARCHAR(500)),           

            CAST('' AS VARCHAR(500))

            ,1           

    FROM   cte

    WHERE  Countour = 1000

      

    UNION ALL

   

    SELECT Id,

           string,

           SUBSTRING(string, StartPoint, CHARINDEX(' ', string,StartPoint)-startPoint)

           ,CHARINDEX(' ', string,StartPoint)+1

           

    FROM   SPLIT

    WHERE  CHARINDEX(' ', string,StartPoint) > 0

)



SELECT TOP (@top) id,

       (

           SELECT LTRIM(b.word + ' ')

           FROM  (SELECT * FROM SPLIT  WHERE StartPoint>1  )  AS b

           WHERE  a.id = b.id 

           GROUP BY b.word

           FOR XML PATH('')

       )   

FROM   test_string AS a  



OPTION(MAXRECURSION 0)

[محمد سلیم آبادی]

پست ویرایش شده است
ممنون از پیگیریتون. با مطرح شدن بحث REPLACE کردن، راه حل ها بهینه تر و هوشمندتر شدن.

در واقع در روش پیشینم من آمدم کاراکتر به کاراکتر رشته ها را مورد بررسی قرار داده و هرگاه کاراکتر مورد نظر ما بود انتخاب در غیر اینصورت انتخاب نخواهد شد و این کاراکتر ها با کاراکتر های پیشین الحاق میشدند. که این عملیات، جداسازی کاراکتر، مقایسه کاراکتر و الحاق آن با کاراکتر های پیشین به اندازه طول رشته انجام میشد.
من اسم روش قبل را روش "غیر هوشمندانه" نامگذاری میکنم. به این علت که اگر هیچ کاراکتر ناخواسته هم در رشته نباشد کل رشته کاراکتر به کاراکتر مورد بررسی قرار می گیرد.

بنده قسمت اول کوئریم که مربوط به حذف کاراکترهای ناخواسته بود را هوشمند کردم (تکنیک REPLACE همانکاری که شما انجام دادین) البته به روش شخصی خودم. در اینجا دیگه خبری از ستون Countour نیست و دیگه کاراکترهای زائد نمیان یکبار تبدیل به ▄ بشن و دوباره حذف بشن. یعنی همون بار اول ختمشون خونده میشه .

و همچنین قسمت دوم که مربوط به Split کردن رشته می شود را نیز هوشمند کردم. در اینجا ابتدا کد مربوط به برش رشته را توسط تابع STUFF بسیار ساده تر کردم یعنی بجای کد زیر:

SUBSTRING(S, CHARINDEX(' ', S) + 1, LEN(S) - CHARINDEX(' ', S) + 1)

این کد را قرار دادم:

STUFF(S,1,CHARINDEX(' ', S),'')

و سپس بعد از جداسازی کلمه از رشته، کلمات مشابه این کلمه را که در رشته وجود دارند را با تابع REPLACE پیدا و با کاراکتر '' تعویض کردم. با این کار دیگر کلمات تکراری مجددا عمل جداسازی روی آنها اتفاق نخواهد افتاد با این کار سرعت اجرای کوئری به دلیل کاهش دستورات بالا خواهد رفت.
منظور این کد هست:

 REPLACE(STUFF(S,1,CHARINDEX(' ', S),''),

                     ' ' + SUBSTRING(S, 1, CHARINDEX(' ', S)), ' ') 

البته اگر کلمات یکسان در رشته پشت سرهم قرار گرفته باشند تابع REPLACE تنها یکی از آنها را حذف خواهد کرد ولی در کل باعث کاهش اجرای دستورات خواهد شد.

بنظر میرسه که این راه حل سریعترین باشه از بین راه حل های مطرح شده تا الان.

WITH PreCTE(id, value) AS

(

    SELECT  id,

            CAST(string_value AS VARCHAR(500))

     FROM test_string

      

     UNION ALL

  

     SELECT id,

            CAST(REPLACE(value, SUBSTRING(value, PATINDEX('%[^ a-z]%', value), 1), '') 

               AS VARCHAR(500))

     FROM PreCTE

     WHERE PATINDEX('%[^ a-z]%', value) > 0

),

 CTE(ID, S, St) AS

(

    SELECT Id,

           CAST(REPLACE(REPLACE(REPLACE(D.value, ' ', ' ▄'), '▄ ', ''), '▄', '') + ' ' AS VARCHAR(500)) ,

           CAST('' AS VARCHAR(15))

    FROM   test_string AS ts

    CROSS APPLY (SELECT TOP 1 value

                 FROM PreCTE AS pc

                 WHERE ts.id = pc.id

                 ORDER BY LEN(pc.value) ASC) AS D

     

    UNION ALL

  

    SELECT Id,

           CAST(

             REPLACE(STUFF(S,1,CHARINDEX(' ', S),''),

                     ' ' + SUBSTRING(S, 1, CHARINDEX(' ', S)), ' ') 

             AS VARCHAR(500)),           

           CAST(SUBSTRING(S, 1, CHARINDEX(' ', S)) AS VARCHAR(15))

    FROM CTE

    WHERE CHARINDEX(' ', S) > 0

) 

SELECT d.id, c.list

FROM test_string d

CROSS APPLY

(

    SELECT LTRIM(''+st)

    FROM CTE

    WHERE cte.id=d.id

    GROUP BY st

    ORDER BY st

    FOR XML PATH('')

)c(list)

OPTION(MAXRECURSION 0);

یک نمونه از تجزیه رشته به کلمات تشکیل دهنده آن در حالت غیر هوشمند:

rnk   st              s

----- --------------- --------------------------------------------------------------------------------------------------------------

1                     hasan reza hasan javad bahman ali mahdi alihasan bahman bahman reza bahador hasan hasan ali reza reza havad 

2     hasan           reza hasan javad bahman ali mahdi alihasan bahman bahman reza bahador hasan hasan ali reza reza havad 

3     reza            hasan javad bahman ali mahdi alihasan bahman bahman reza bahador hasan hasan ali reza reza havad 

4     hasan           javad bahman ali mahdi alihasan bahman bahman reza bahador hasan hasan ali reza reza havad 

5     javad           bahman ali mahdi alihasan bahman bahman reza bahador hasan hasan ali reza reza havad 

6     bahman          ali mahdi alihasan bahman bahman reza bahador hasan hasan ali reza reza havad 

7     ali             mahdi alihasan bahman bahman reza bahador hasan hasan ali reza reza havad 

8     mahdi           alihasan bahman bahman reza bahador hasan hasan ali reza reza havad 

9     alihasan        bahman bahman reza bahador hasan hasan ali reza reza havad 

10    bahman          bahman reza bahador hasan hasan ali reza reza havad 

11    bahman          reza bahador hasan hasan ali reza reza havad 

12    reza            bahador hasan hasan ali reza reza havad 

13    bahador         hasan hasan ali reza reza havad 

14    hasan           hasan ali reza reza havad 

15    hasan           ali reza reza havad 

16    ali             reza reza havad 

17    reza            reza havad 

18    reza            havad 

19    havad           

در حالت اصلاح شده:

rnk    st              s

------ --------------- ------------------------------------------------------------------------------------------------------------

1                      hasan reza hasan javad bahman ali mahdi alihasan bahman bahman reza bahador hasan hasan ali reza reza havad 

2      hasan           reza javad bahman ali mahdi alihasan bahman bahman reza bahador hasan ali reza reza havad 

3      reza            javad bahman ali mahdi alihasan bahman bahman bahador hasan ali reza havad 

4      javad           bahman ali mahdi alihasan bahman bahman bahador hasan ali reza havad 

5      bahman          ali mahdi alihasan bahman bahador hasan ali reza havad 

6      ali             mahdi alihasan bahman bahador hasan reza havad 

7      mahdi           alihasan bahman bahador hasan reza havad 

8      alihasan        bahman bahador hasan reza havad 

9      bahman          bahador hasan reza havad 

10     bahador         hasan reza havad 

11     hasan           reza havad 

12     reza            havad 

13     havad  

[cherchil_hra]

با استفاده از دستور replace و تغییر کوئریتون (پست آخر) سرعتش با قبل از تغییر خیلی تفاوت کرد، مثلا روی 2000 رکورد 48 ثانیه طول می کشید ولی اینبار 38 ثانیه

1000 رکورد: برای شما 10 ثانیه | برای من 11 ثانیه
2000 رکورد: برای شما 38 ثانیه | برای من 40 ثانیه
3000 رکورد: برای شما 1:24 | برای من 1:29

بسیار عالی!

علت استفاده از countour در کوئری من این هستش که بتونم سریع ردیف های اصلاح شده پایانی رو پیدا بکنم (مقادیری که برابر 1000 هستند) و علت استفاده از کاراکتر '▄' برای اینکه طول رشته حفظ بشه؛ در غیر این صورت ممکنه مقدار کنتور بیشتر از طول رشته بشه و شرط برقرار نشه و رشته نیمه کاره رها بشه.

توابع خود sql هم که سرعتش بسیار عالی است؛ خیلی سریع یک رشته رو از اول تا آخر می گردند و کلمه مورد نظر رو پیدا کرده و مقدار داده شده رو جایگزینش می کنند و یا محل یک کاراکتر خاص رو بر می گردونند. ما هم همین کار رو کردیم ولی زور ما که بهشون نرسید...

با توجه به فرمایش شما و حذف مقادیر تکراری با replace، کوئری خودم رو به این شکل تغییر دادم:

DECLARE @top INT = 3000





;WITH CTE (ID,MyString,Countour) AS

(

    SELECT TOP(@top) 

		   id,

           string_value ,

           1                    

    FROM   test_string

    

    UNION ALL

    

    SELECT id,

           CAST(

               CASE 

                    WHEN SUBSTRING(MyString, Countour, 1) LIKE '[a-z ▄]' THEN MyString

                    ELSE REPLACE(MyString, SUBSTRING(MyString, Countour, 1), '▄')

               END AS VARCHAR(500)

           )   MyString,

           CASE 

                WHEN PATINDEX('%[^a-z ▄]%', mystring) = 0 THEN 1000

                ELSE PATINDEX('%[^a-z ▄]%', mystring)

           END

    FROM   CTE

    WHERE  Countour <= LEN(MyString) + 1

)



, SPLIT(ID, string, word) AS

(

        

    SELECT id,

            CAST( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(mystring, '▄',''), ' ', ' ▄'), '▄ ', ''), '▄', ''),CHAR(1), '') + ' ' AS VARCHAR(500)),           

            CAST('' AS VARCHAR(500))

    FROM   cte

    WHERE  Countour = 1000

      

    UNION ALL      

    

    SELECT Id,

           CAST( REPLACE(STUFF(string,1,CHARINDEX(' ', string),'')

                 ,' ' + SUBSTRING(string,1, CHARINDEX(' ', string)) 

                 ,' '

             ) AS VARCHAR(500)),          

           CAST(SUBSTRING(string, 1, CHARINDEX(' ', string)) AS VARCHAR(500))

    FROM SPLIT

    WHERE CHARINDEX(' ', string) > 0

)

 

SELECT TOP (@top) id,

       (

           SELECT LTRIM(b.word + '')

           FROM  SPLIT AS b

           WHERE  a.id = b.id 

           GROUP BY b.word

           FOR XML PATH('')

       )   

FROM   test_string AS a  





OPTION(MAXRECURSION 0)

نتایج کسب شده:

1000 رکورد: برای شما 10 ثانیه | برای من 9 ثانیه
2000 رکورد: برای شما 38 ثانیه | برای من 32 ثانیه
3000 رکورد: برای شما 1:24 | برای من 1:09

[محمد سلیم آبادی]

علت استفاده از countour در کوئری من این هستش که بتونم سریع ردیف های اصلاح شده پایانی رو پیدا بکنم (مقادیری که برابر 1000 هستند) و علت استفاده از کاراکتر '▄' برای اینکه طول رشته حفظ بشه؛ در غیر این صورت ممکنه مقدار کنتور بیشتر از طول رشته بشه و شرط برقرار نشه و رشته نیمه کاره رها بشه.

متوجه شدم علت اینکار چی هست.
قسمت اول راه حلتون رو مورد بررسی قرار دادم. متوجه شدم میشه عبارات CASE WHEN را از راه حل کاملا حذف نمود (که قطعا بدلیل کاهش دستورات و محاسبات موجب افزایش سرعت خواهد شد) یعنی به این شکل:

;WITH CTE (ID,MyString,Countour) AS

(

    SELECT TOP(@top)

           id,

           string_value ,

           PATINDEX('%[^a-z ▄]%', string_value)                   

    FROM   test_string

     

    UNION ALL

     

    SELECT id,

           CAST(REPLACE(MyString, SUBSTRING(MyString, Countour, 1), '▄') AS VARCHAR(500)),

           PATINDEX('%[^a-z ▄]%', mystring)

    FROM   CTE

    WHERE  Countour > 0

)

کوئری تا مادامی اجرا میشه که مقدار Countour بزرگتر از 0 باشه. و توجه داشته باشید که در عبارت SPLIT شرط Countour = 0 را باید لحاظ کنید تا نتیجه درست برگردانده شود.

[cherchil_hra]

بسیار عالی! کوئری خواناتر شد و تعداد رکوردهای برگشتی قسمت cte کمتر.

کوئری ها رو، روی 4000 رکورد مشابه تست کردم و نتیجه یکسان بود: 1.59

تعداد رکوردها با اصلاح کوئری توسط شما در قسمت cte کمتر میشه ولی در نهایت در قسمت split چون از شرط Countour = 0 و یا Countour = 1000 استفاده می شود، در هر دو کوئری تعداد رکوردهای قسمت split با هم برابر خواهند شد.

اما، با توجه به شرط Countour=0 دیگه به کاراکتر '▄' نیازی نداریم و در قسمت split یک replace حذف میشه که تاثیرش از 1 تا 6 ثانیه روی تعداد رکوردهای 500 تا 5000 هستش:

;WITH CTE (ID,MyString,Countour) AS

(

    SELECT TOP(@top)

           id,

           string_value,

           PATINDEX('%[^a-z ]%', string_value)                 

    FROM   test_string

      

    UNION ALL

      

    SELECT id,

           CAST(REPLACE(MyString, SUBSTRING(MyString, PATINDEX('%[^a-z ]%', mystring), 1), '') AS VARCHAR(500)),

           PATINDEX('%[^a-z ]%', mystring)

    FROM   CTE

    WHERE  Countour > 0

)

و درنهایت :

DECLARE @top INT =5000





;WITH CTE (ID,MyString,Countour) AS

(

    SELECT TOP(@top)

           id,

           string_value,

           PATINDEX('%[^a-z ]%', string_value)                 

    FROM   test_string

      

    UNION ALL

      

    SELECT id,

           CAST(REPLACE(MyString, SUBSTRING(MyString, PATINDEX('%[^a-z ]%', mystring), 1), '') AS VARCHAR(500)),

           PATINDEX('%[^a-z ]%', mystring)

    FROM   CTE

    WHERE  Countour > 0

)



, SPLIT(ID, string, word) AS

(

         

    SELECT id,

CAST( REPLACE(REPLACE(REPLACE(mystring, ' ', ' ▄'), '▄ ', ''), '▄', '') + ' ' AS VARCHAR(500))      

            CAST('' AS VARCHAR(500))

    FROM   cte

    WHERE  Countour = 0

       

    UNION ALL     

     

    SELECT Id,

           CAST( REPLACE(STUFF(string,1,CHARINDEX(' ', string),'')

                 ,' ' + SUBSTRING(string,1, CHARINDEX(' ', string))

                 ,' '

             ) AS VARCHAR(500)),         

           CAST(SUBSTRING(string, 1, CHARINDEX(' ', string)) AS VARCHAR(500))

    FROM SPLIT

    WHERE  CHARINDEX(' ', string)  > 0

)

  

SELECT TOP (@top) id,

       (

           SELECT LTRIM(b.word + '')

           FROM  SPLIT AS b

           WHERE  a.id = b.id

           GROUP BY b.word

           FOR XML PATH('')

       )  

FROM   test_string AS a 





OPTION(MAXRECURSION 0)

[محمد سلیم آبادی]

REPLACE(mystring, '▄','')

شما ابتدا این دستور رو حذف کردین سپس سرجاش قرار دادین، همانطور که خودتون اشاره کردین دیگه نیازی به این تیکه کد نیست. البته بنظرم اشتباه سهوی بوده

کوئری ها رو، روی 4000 رکورد مشابه تست کردم و نتیجه یکسان بود: 1.59

فک میکردم سرعتش بالاتر بره...

[cherchil_hra]

REPLACE(mystring, '▄','')

شما ابتدا این دستور رو حذف کردین سپس سرجاش قرار دادین، همانطور که خودتون اشاره کردین دیگه نیازی به این تیکه کد نیست. البته بنظرم اشتباه سهوی بوده

فک میکردم سرعتش بالاتر بره...

اصلاح شد!
به خاطر این بود که داشتم امتحان می کردم ببینم replace که برای فضای خالی نوشتید، یک قسمتش اضافه هست یا نه؟این قسمت رو حذف کردم، اون یکی رو یادم رفت .

REPLACE([رشته],CHAR(1), '')

در کل ممنون! بسیار آموختم.

[محمد سلیم آبادی]

باید ماکروسافت از ما ممنون باشه که اینقدر داریم به نرم افزارش می پردازیم
حقیقتش اولین راه حلی که ایجاد کردم بر اساس جدول اعداد بود ولی خب خیلی پیچیده از آب در اومد، گفتم بیام از پیچیدگیش بکاهم و کل ماجرا رو بسپارم به جدول اعداد (و برای قضاوت نهایی اینکه راه حل بر اساس جدول اعداد مناسب تر هست یا replace جاداده شده در recursive cte). به این شکل که اول تمام رشته را کاراکتر به کاراکتر تفکیک کنم و آنهایی که ناخواسته هستند رو حذف و ما بقی رو دوباره الحاق کنم. سپس رشته الحاق شده رو دوباره کلمه به کلمه تفکیک کنم و بعد از حذف کلمات مشابه (گروه بندی) دوباره این کلمات رو الحاق کنم.
کدی که در ادامه مشاهده میشه رو تست نکردم (فقط داخل word تایپ شده به این خاطر که sql server ام دچار مشکل شده) اگه مشکلی داشت خودتون زحمت اصلاحشو بکشید.

فقط شما لطف کنید ابتدا یک جدول اعداد درست کنید که دارای یک ستون به نام N باشه با مقادیر 1 تا مثلا 4000 (حدکثر طول رشته که تعریف شده)- فراموش نکنید که یک Index روی ستون N ایجاد کنید که موقع فیلتر شدن سرعتش حفظ بشه.
مثلا با این کد:

select top(500) row_number()over(order by null) as n into number_table from sys.columns as s, sys.columns as ss 

سپس کوئری زیر رو روی داده هاتون تست کنید ببینید سرعتش قابل قیاس با آخرین راه حل هست یا خیر.

ویرایش:
چنتا مورد کوچیک بود که برطرفش کردم(الان قابل محک زدن هست):

/*========FIRST PHASE: Removing Unwanted Characters======*/

-- Splitting Strings And Removing Unwanted Characters

WITH CTE1 AS

(

    SELECT id, d.chr

      FROM test_string AS S

     CROSS APPLY (SELECT n, SUBSTRING(string_value, n, 1)AS chr

                    FROM number_table

                   WHERE n <= LEN(string_value)

                     AND SUBSTRING(string_value, n, 1) LIKE '[ a-z]') AS D

),

-- Concatenating Separated Characters

 CTE2 AS

(

    SELECT ts.id, ' ' + REPLACE(REPLACE(REPLACE(d2.strg, ' ', ' ▄'), '▄ ', ''), '▄', '') + ' ' AS string_value

    FROM test_string AS ts

    CROSS APPLY (SELECT LTRIM('~!@' + chr)

                   FROM CTE1

                  WHERE ts.id = id              

                    FOR XML PATH('')) AS D(l)

    CROSS APPLY (SELECT REPLACE(D.l, '~!@', '')) D2(strg)

),

/*==SECOND PHASE: Removing Duplicate Words And Sorting Them==*/

-- Splitting Words

 CTE3 AS

(

    SELECT id, wds

      FROM CTE2

     CROSS APPLY (SELECT SUBSTRING(string_value, n + 1, CHARINDEX(' ', string_value, n + 1) - n)

                    FROM number_table

                   WHERE SUBSTRING(string_value, n, 1) = ' '

                     AND n <= LEN(string_value) - 1) AS D(wds)

),

-- Removing Duplicate Words, Sorting, Concanating

 CTE4 AS

(

    SELECT ts.id, STUFF(wds, 1, 1, '') AS wds

    FROM test_string AS ts

    CROSS APPLY (SELECT RTRIM(' ' + wds)

                   FROM CTE3

                  WHERE ts.id = id

                  GROUP BY wds 

                    FOR XML PATH('')) D(wds)

)

SELECT * FROM CTE4;

[cherchil_hra]

امان از word، داشتم نگاه می کردم آخه منها چه ایرادی داره که sql ازش خطا می گیره

Incorrect syntax near '–'.

نگو منهاش، منها نبود...

یکم تغییرات روی cte1 و cte2 دادم:
1. جای کاراکتر space رو با '▄' عوض کردم. چون موقع اتصال رشته ها توی cte2، تمام فاصله ها از بین میره.
2. بنابراین داخل cte2 یک replace اضافه شد

cte3 کلمات رو به درستی برنمی گردونه!

این از cte1 و cte2 و cte3

DECLARE @top INT =1000;



;WITH CTE1 (id,chr) AS

(

    SELECT  id,

           replace(d.chr,' ','▄')

    FROM   test_string  AS S

   

    CROSS APPLY (

        SELECT n,

               SUBSTRING(string_value, n, 1) AS chr

        FROM   number_table

        WHERE  n <= LEN(string_value) AND (SUBSTRING(string_value, n, 1) LIKE '[ a-z]')

    ) AS D

    ---------------------------------

    WHERE id < @top

    ---------------------------------

),

-- Concatenating Separated Characters

 CTE2 (id,string_value) AS

(

    SELECT TOP(@top) ts.id,

         REPLACE(REPLACE(REPLACE(REPLACE(d.l, '▄', ' '), ' ', ' ▄'), '▄ ', ''), '▄', '') + ' '

    FROM   test_string  AS ts

    CROSS APPLY (

        SELECT rtrim( chr )

        FROM   CTE1

        WHERE  ts.id = id

        --ORDER BY n

        FOR XML PATH('') )  AS D(l)

),

CTE3 (id,wds) AS

(

    SELECT id, wds

    FROM CTE2

    CROSS APPLY (SELECT SUBSTRING(string_value, n + 1, CHARINDEX(' ', string_value, n + 1) - 1)

             FROM number_table

             WHERE SUBSTRING(string_value, n, 1) = ' '

             AND n <= LEN(string_value) - 1) AS D(wds)

)

[محمد سلیم آبادی]

لطف به پست قبلیم مراجعه کنید. برای اینکه کمتر شلوغ بشه همون پست قبلیم رو ویرایش کردم.

[cherchil_hra]

لطف به پست قبلیم مراجعه کنید. برای اینکه کمتر شلوغ بشه همون پست قبلیم رو ویرایش کردم.

روی 200 رکورد (داده های قبلی) : 35 ثانیه
21 ثانیه اش صرف cte3 میشه

[behrouzlo]

من یک کم درگیر یک کاری هستم زیاد نتونستم توی بحثها شرکت کنم. محمد توی راه حلت چرا CTE1 و CTE2 را باهم ترکیب نمی کنی. یعنی شرط به این صورت باشه که اگر دربازه a-z بود یا که اگر کاراکتر جاری اسپیس باشه و کاراکتر بعدی در بازه a - z بود کاراکتر جاری برگشت داده بشه در غیر اینصورت چیزی برگشت داده نشه. به این صورت ما فقط یک کاراکتر اسپیس خواهیم داشت و CTE2 حذف خواهد شد.

[محمد سلیم آبادی]

CTE1 و CTE2 ادغام شدن.

WITH 

 CTE2 AS

(

    SELECT ts.id, ' ' + REPLACE(REPLACE(REPLACE(d2.strg, ' ', ' ▄'), '▄ ', ''), '▄', '') + ' ' AS string_value

    FROM test_string AS ts

    CROSS APPLY (SELECT LTRIM('~!@' + SUBSTRING(string_value, n, 1))

                   FROM number_table

                  WHERE n <= LEN(string_value) 

                    AND ts.id = id             

                    AND SUBSTRING(string_value, n, 1) LIKE '[ a-z]'

                    FOR XML PATH('')) AS D(l)

    CROSS APPLY (SELECT REPLACE(D.l, '~!@', '')) D2(strg)

),

 CTE3 AS

(

    SELECT id, wds

      FROM CTE2

     CROSS APPLY (SELECT SUBSTRING(string_value, n + 1, CHARINDEX(' ', string_value, n + 1) - n)

                    FROM number_table

                   WHERE SUBSTRING(string_value, n, 1) = ' '

                     AND n <= LEN(string_value) - 1) AS D(wds)

),

 CTE4 AS

(

    SELECT ts.id, STUFF(wds, 1, 1, '') AS wds

    FROM test_string AS ts

    CROSS APPLY (SELECT RTRIM(' ' + wds)

                   FROM CTE3

                  WHERE ts.id = id

                  GROUP BY wds

                    FOR XML PATH('')) D(wds)

)

SELECT * FROM CTE4;

[cherchil_hra]

روی 200 رکورد (داده های قبلی) : 35 ثانیه
21 ثانیه اش باز صرف cte3 میشه

زمانش مثل قبلی هست!

[محمد سلیم آبادی]

من فقط به سفارش دوستمون query رو reform کردم، می دونستم در عملکرد تغییر خاصی نخواهد کرد.
فکر کنم شما آخرین پست این تاپیک را هنوز مشاهده نکردین:
https://barnamenevis.org/showthread.p...A8%D8%A7%D8%AA)