هشتمین چالش 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 کردم، می دونستم در عملکرد تغییر خاصی نخواهد کرد.
فکر کنم شما آخرین پست این تاپیک را هنوز مشاهده نکردین:
http://barnamenevis.org/showthread.php?375939-%D9%86%D9%87%D9%85%DB%8C%D9%86-%DA%86%D8%A7%D9%84%D8%B4-SQL-(%D8%AC%D8%B3%D8%AA%D8%AC%D9%88%DB%8C-%D8%A7%D9%84%DA%AF%D9%88-%D8%AA%D8%B1%DA%A9%DB%8C%D8%A8%D8%A7%D8%AA)