پیدا کردن عناوین مرتبط یا شبیه به هم

[b.paseban]

سلام بر شما.
چطوری میشه توی یه دیتا بیس عناوین شبیه به هم رو پیدا کرد.منظورم عناوین تکراری یا dublicate نیست یه چیزایی شبیه به similar post که تو بعضی از سایت ها هم هست که پست های مرتبط به هم رو نمایش میدن.
مثلا مثل همین پست که خروجی مثل زیر بده:

پیدا کردن عناوین مرتبط یا شبیه به هم
پیدا کردن مقادیر شبیه
عناوین مرتبط
عناوین شبیه به هم

من از یه کد استفاده کردم اما فقط همون kayword من رو نمایش داد یعنی "پیدا کردن عناوین مرتبط یا شبیه به هم".
کد من اینه:


ALTER Procedure [dbo].[sp_SelectAllLikeMMReports]
@Ownkeyword nvarchar(300),
As
Begin
SELECT Tbl_CategoryMMReport.Title , Tbl_CategoryMMReport.CategoryMMReportID
FROM Tbl_CategoryMMReport
WHERE (Tbl_CategoryMMReport.Title LIKE N'%' + @Ownkeyword + N'%')
)
End

بنطر من باید که کلمه مورد جستجو ("پیدا کردن عناوین مرتبط یا شبیه به هم") رو به تکه های مختلف تقسیم کرد و بعدشم چند تا ورودی و رو به دیتا بیس بدیم و خروجی رو دریافت کنید یه چیزی شبیه به این کد:


ALTER Procedure [dbo].[sp_SelectAllLikeMMReports]
@Ownkeyword nvarchar(300),
@Twokeyword nvarchar(300),
@Threekeyword nvarchar(300),
@Fourkeyword nvarchar(300)
As
Begin
SELECT Tbl_CategoryMMReport.Title , Tbl_CategoryMMReport.CategoryMMReportID
FROM Tbl_CategoryMMReport
WHERE (Tbl_CategoryMMReport.Title LIKE N'%' + @Ownkeyword + N'%'
OR Tbl_CategoryMMReport.Title LIKE N'%' + @Twokeyword + N'%'
OR Tbl_CategoryMMReport.Title LIKE N'%' + @Threekeyword + N'%'
OR Tbl_CategoryMMReport.Title LIKE N'%' + @Fourkeyword + N'%'
)
End

ولی میخوام بدونم چطوری میشه یه رشته رو تبدیل به چند تا قسمت کرد و بر اساس همون قسمت ها جستجو رو انجام داد. شایدم باید کلمات کلیدی رو از تو رشته فقط پیدا کرد و جستجو رو انجام داد.
تو کد دوم من 4 ورودی میگریم ولی شاید تو یه جا برای بهتر انجام دادن بیشتر از 4 تا لازم باشه دریافت کرد.
ممنون میشم راهنمایی کنید.

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

ولی میخوام بدونم چطوری میشه یه رشته رو تبدیل به چند تا قسمت کرد و بر اساس همون قسمت ها جستجو رو انجام داد.قبلش این تابع رو ایجاد کنید:
--TVF for Splitting the String
CREATE FUNCTION dbo.splitter (@S VARCHAR(MAX), @D CHAR(1)) RETURNS TABLE AS
RETURN (SELECT CASE WHEN CHARINDEX(@D, @S + @D, nbr) - nbr = 0 THEN ''
ELSE SUBSTRING(@S, nbr, CHARINDEX(@D, @S + @D, nbr) - nbr)
END AS Word, nbr
FROM (SELECT TOP(300) ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM Sys.columns) AS Nums(nbr)
WHERE nbr <= LEN(@S)
AND SUBSTRING(@D + @S, nbr, 1) =@D);
GO

سپس sp تون رو به این شکل تغییر بدین، من فرض گرفتم شما کلمات رشته رو با کارکتر خط تیره از هم جدا کردین. sp یک پارامتر ورودی داره از نوع رشته که میتونه شامل چندین کلمه که با کاراکتر خط تیره از هم جدا شدن باشن:
ALTER Procedure [dbo].[sp_SelectAllLikeMMReports]
@keywords nvarchar(300)
As
Begin
WITH CTE AS
(SELECT Word
FROM dbo.splitter(@keywords, '-'))
SELECT Title , CategoryMMReportID
FROM Tbl_CategoryMMReport AS T
WHERE EXISTS
(SELECT *
FROM CTE C
WHERE T.Title LIKE N'%' + Word + N'%');
End

[mohsen24000]

این کوئری خوبه ولی بهتر بود اگر قراره مشابه ها رو استخراج کنه یه رنج حداقلی هم براش تعریف میشد! در اینجا حداقل یعنی 1.
مثلا اونهایی که حداقل 2کلمه مشابه دارند یا اونهایی که حداقل نیمی از کلمات مشابه رو دارند... اونوقت جذاب تر بود.:چشمک:
----------------------
البته من مجبور شدم به خاطر تفاوت Collationها از این کد استفاده کنم:

declare @tt nvarchar(200)='شخص ثالث';
WITH CTE AS
(SELECT Word COLLATE arabic_100_ci_ai word
FROM splitter(@tt, ' '))
SELECT id,qcode,qTitle , qcontext
FROM dbQuestion AS T
WHERE EXISTS
(SELECT *
FROM CTE C
WHERE T.qcontext LIKE N'%' + Word + N'%');

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

این کوئری خوبه ولی بهتر بود اگر قراره مشابه ها رو استخراج کنه یه رنج حداقلی هم براش تعریف میشد! در اینجا حداقل یعنی 1.
مثلا اونهایی که حداقل 2کلمه مشابه دارند یا اونهایی که حداقل نیمی از کلمات مشابه رو دارند... اونوقت جذاب تر بود.:چشمک:
یعنی میگی این جواب نمیده؟
WHERE (SELECT COUNT(*)
FROM CTE C
WHERE T.Title LIKE N'%' + Word + N'%') > @YOUR_VALUE_HERE;

[mohsen24000]

یعنی میگی این جواب نمیده؟
WHERE (SELECT COUNT(*)
FROM CTE C
WHERE T.Title LIKE N'%' + Word + N'%') > @YOUR_VALUE_HERE;
Invalid length parameter passed to the LEFT or SUBSTRING function.

[b.paseban]

اما خروجی درستی نمیده. من زیاد با sqlserver کار نکردم ممنون میشم بگین این مراحلی که من رفتم درسته یا نه.
من از طریق New Query اقدام به ساخت یه کوئری کردم که همون کد تابع شما رو داخل اون قرار داد البته دو خط اولش رو به معرفی دیتابیس مورد نظر پرداختم :


USE[MyDb]
GO
--TVF for Splitting the String
CREATE FUNCTION dbo.splitter (@S VARCHAR(MAX), @D CHAR(1)) RETURNS TABLE AS
RETURN (SELECT CASE WHEN CHARINDEX(@D, @S + @D, nbr) - nbr = 0 THEN ''
ELSE SUBSTRING(@S, nbr, CHARINDEX(@D, @S + @D, nbr) - nbr)
END AS Word, nbr
FROM (SELECT TOP(300) ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM Sys.columns) AS Nums(nbr)
WHERE nbr <= LEN(@S)
AND SUBSTRING(@D + @S, nbr, 1) =@D);
GO

بعدشم دوباره از طریق New Query واسه ساختن یه Sp به شکل زیر:


ALTER Procedure [dbo].[sp_SelectAllMMM]
@keywords nvarchar(300)
As
Begin
WITH CTE AS
(SELECT Word
FROM dbo.splitter(@keywords,'-'))
SELECT Title , CategoryMMReportID
FROM Tbl_CategoryMMReport AS T
WHERE EXISTS
(SELECT *
FROM CTE C
WHERE T.Title LIKE N'%' + Word + N'%');
End

Function ایجاد شده داخل programmability>Functions>Table-Valued Function ساخته شده.sp هم که داخل stored procedure دیگه.
بعد یه ورودی بطور مثال :
پیدا-کردن-عناوین-مرتبط-یا-شبیه-به-هم
ولی خروجی مورد نظر رو نمیده.(ورودی مثال زده شده همین متن نبود یکی از متنایی که زیاد تو دیتا بیس استفاده شده بود.)

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

اما خروجی درستی نمیده. من زیاد با sqlserver کار نکردم ممنون میشم بگین این مراحلی که من رفتم درسته یا نه.بنظر راهی که رفتی درست. اینکه خروجی مورد نظر رو نمیده، نمیدونم واقعن چرا...
ولی به پست 3 یک نیم نگاهی بنداز ببین میتونی با تغییر Collation ستون word نتیجه مورد نظرت رو بگیری.

Invalid length parameter passed to the LEFT or SUBSTRING function.از کوئری زیر به جای کوئری سابق استفاده کنید:
declare @tt nvarchar(200)='hasan reza';

SELECT id,q,count(word) cnt
FROM (values(1,'hasan reza hosein'),
(2,'javad reza qoli reza'),
(3, 'mahdi mamad')) AS T(id,q)
OUTER APPLY (SELECT *
FROM splitter(@tt, ' ') C
WHERE T.q LIKE N'%' + Word + N'%')d
group by id,q;

[b.paseban]

من متوجه کد شما نشدم.ظاهراً شما تعداد کاراکترهای تکرار شده hasan reza رو داخل مقادیر تعیین شده در سطر بعد رو جستجو می کنید؟(که توی این کد cnt بترتیب خروجی های 2و1و0 رو میده)
حالا من بخوام توی دیتابیس مقدار مورد نظر خودم رو جستجو کنم باید چه تغییری داخل کد بوجود بیارم؟
مثلا فیلد NewsID , Title ور داخل جدول Tbl_News جستجو کنم.ورودی دریافتی من تو کجا قرار میگیره(کد جدید)
من باید این کد رو بعنوان یه Sp بکار ببرم یا ...؟
با تشکر از دوستان.

[mohsen24000]

declare @tt nvarchar(200)='hasan reza';

SELECT NewsID , Title,count(word) cnt FROM Tbl_News AS T
OUTER APPLY (SELECT *
FROM splitter(@tt, ' ') C
WHERE T.title LIKE N'%' + Word + N'%')d
group by NewsID , Title;

[b.paseban]

توی عکس زیر نشون میده که جستجوی کلمه 'محیط زیست' داخل دیتا بیس هیچ نتیجه ای رو در بر نداره درصورتی که داخل دیتابیس در چند سطر کلمه 'محیط زیست' تکراز شده.
توی عکس کدی که شما قرار دادین رو گذاشتم و نتیجه جستجو هم مشخص.
مشکل از کجاست؟
http://dc99.4shared.com/img/8307nqmt/s7/0.3703240434259598/Untitled-1.jpg

[mohsen24000]

این بخاطر نوع حرف "ی" هست. شما با Shift+x حرف "ی" رو تایپ کن، احتمال 99درصد جواب میده...

[b.paseban]

در مورد کلمه تهران هم همین نتیجه دریافت میشه:
http://dc596.4shared.com/img/uSzAR5JQ/s7/0.4619270348232193/tehran.jpg

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

کوئری با زبان انگلیسی مشکلی نداره حالا با داده های فارسی جواب نمیده مسالس.
یکاری کن شما، بیا collation ستون title رو از طریق زیر بدست بیار(tbl_news نام جدول، title نام ستون):
select collation_name from sys.columns
where object_id = (
select object_id from sys.objects where type='U'
and name='tbl_news')
and name='title';


حالا مقدار بدست اومده رو به بعد از word در شرط where لحاظ کنید (مثلا من فرض کردم collation ستون title برابر است با Persian_100_CI_AS)
LIKE N'%' + Word COLLATE Persian_100_CI_AS + N'%'

گرچه بعید میدونم این روش هم جواب بده، چرا که اگه collation ها تداخل داشتن پیام خطا صادر میشد.

[b.paseban]

اگه کدها رو درست قرار داده باشم خروجی باز هم مثل قبله.(به کدهایی که من گذاشتم اگه میشه با دقت نگاه کنید چون من زیاد مطمئن نیستم درست کدها رو قرار دادم یا نه)
http://dc249.4shared.com/img/2DZh5apE/s7/0.1373092883259287/1_online.jpg