منتدى فيجوال بيسك لكل العرب | منتدى المبرمجين العرب قسم قواعد البيانات قسم مقالات SQL SERVER معلومة --- البحث الصوتي مع الدالة --- Difference & soundex --- مهم جداً
موضوع مغلق 
تقييم الموضوع :
  • 0 أصوات - بمعدل 0
  • 1
  • 2
  • 3
  • 4
  • 5
ادوات الموضوع
معلومة --- البحث الصوتي مع الدالة --- Difference & soundex --- مهم جداً
RaggiTech غير متصل
مراقب منتدى
المشاركات : 2,523
المواضيع 707
الإنتساب : Sep 2012
السمعة : 48
الشكر: 91
تم شكره 1120 مرات في 307 مشاركات
#5
21-10-12, 05:51 PM



وجه مظلم أخر

الحقول التي بها كلمتين لا تصلح مع هذه الدالة ، كما أنها تسير حرف حرف كما وضحنا إذن الكلمات الكبيرة تقع فى مشكلة كما أن الكلمات التى بها حروف خاصة مثل كلمة

آل بدر Ul-Badr

سيحصل على الحرف الأول والثاني وعندما يصل للشرطة سيتجاهلها ويتجاهل بقية الحقل


رغم هذا السوء أري أن هذا الدالة مفيدة بنسبة كبيرة وتفيد المستخدم ويجب على المطورين التعامل بها بجانب تعرفهم على الخاصية FullTextSearch الهامة والتى يتمحور حولها مفهوم وعلم البحث الحديث

وأري أنه يجب على ميكروسوفت أن تبدأ فى التطوير فمن اصدار 2000 حتى الآن هذه الدالة محلك سر



هذا الاسكريبت لانشاء الجدول والبيانات التي ضربنا عليها المثال


كود :
[COLOR=#000000][COLOR=#0000bb]SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING[/COLOR][color=#007700], [/color][color=#0000bb]ANSI_WARNINGS[/color][color=#007700], [/color][color=#0000bb]CONCAT_NULL_YIELDS_NULL[/color][color=#007700], [/color][color=#0000bb]ARITHABORT[/color][color=#007700], [/color][color=#0000bb]QUOTED_IDENTIFIER[/color][color=#007700], [/color][COLOR=#0000bb]ANSI_NULLS ON
GO
[/COLOR][color=#007700]IF [/color][color=#0000bb]EXISTS [/color][color=#007700]([/color][color=#0000bb]SELECT [/color][color=#007700]* [/color][color=#0000bb]FROM tempdb[/color][color=#007700]..[/color][color=#0000bb]sysobjects WHERE id[/color][color=#007700]=[/color][color=#0000bb]OBJECT_ID[/color][color=#007700]([/color][color=#dd0000]'tempdb..#tmpErrors'[/color][color=#007700])) [/color][color=#0000bb]DROP TABLE [/color][COLOR=#ff8000]#tmpErrors
[/COLOR][COLOR=#0000bb]GO
CREATE TABLE [/COLOR][COLOR=#ff8000]#tmpErrors (Error int)
[/COLOR][COLOR=#0000bb]GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
[/COLOR][color=#007700]PRINT [/color][color=#0000bb]N[/color][COLOR=#dd0000]'Creating [dbo].[FriendsData]'
[/COLOR][COLOR=#0000bb]GO
CREATE TABLE [/COLOR][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][COLOR=#007700]]
(
[[/COLOR][color=#0000bb]ID[/color][color=#007700]] [[/color][color=#0000bb]int[/color][color=#007700]] [/color][color=#0000bb]NOT NULL IDENTITY[/color][color=#007700]([/color][color=#0000bb]1[/color][color=#007700], [/color][color=#0000bb]1[/color][COLOR=#007700]),
[[/COLOR][color=#0000bb]FirstName[/color][color=#007700]] [[/color][color=#0000bb]nvarchar[/color][color=#007700]] ([/color][color=#0000bb]50[/color][color=#007700]) [/color][color=#0000bb]COLLATE Arabic_CI_AS NOT NULL[/color][COLOR=#007700],
[[/COLOR][color=#0000bb]LastName[/color][color=#007700]] [[/color][color=#0000bb]nvarchar[/color][color=#007700]] ([/color][color=#0000bb]50[/color][color=#007700]) [/color][COLOR=#0000bb]COLLATE Arabic_CI_AS NOT NULL
[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000bb]GO
[/COLOR][color=#007700]IF @@[/color][color=#0000bb]ERROR[/color][color=#007700]<>[/color][color=#0000bb]0 [/color][color=#007700]AND @@[/color][color=#0000bb]TRANCOUNT[/color][color=#007700]>[/color][COLOR=#0000bb]0 ROLLBACK TRANSACTION
GO
[/COLOR][color=#007700]IF @@[/color][color=#0000bb]TRANCOUNT[/color][color=#007700]=[/color][color=#0000bb]0 BEGIN INSERT INTO [/color][COLOR=#ff8000]#tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
[/COLOR][COLOR=#0000bb]GO
[/COLOR][color=#007700]PRINT [/color][color=#0000bb]N[/color][COLOR=#dd0000]'Creating primary key [PK_FriendsData] on [dbo].[FriendsData]'
[/COLOR][COLOR=#0000bb]GO
ALTER TABLE [/COLOR][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] [/color][color=#0000bb]ADD CONSTRAINT [/color][color=#007700][[/color][color=#0000bb]PK_FriendsData[/color][color=#007700]] [/color][color=#0000bb]PRIMARY KEY CLUSTERED [/color][color=#007700]([[/color][color=#0000bb]ID[/color][COLOR=#007700]])
[/COLOR][COLOR=#0000bb]GO
[/COLOR][color=#007700]IF @@[/color][color=#0000bb]ERROR[/color][color=#007700]<>[/color][color=#0000bb]0 [/color][color=#007700]AND @@[/color][color=#0000bb]TRANCOUNT[/color][color=#007700]>[/color][COLOR=#0000bb]0 ROLLBACK TRANSACTION
GO
[/COLOR][color=#007700]IF @@[/color][color=#0000bb]TRANCOUNT[/color][color=#007700]=[/color][color=#0000bb]0 BEGIN INSERT INTO [/color][COLOR=#ff8000]#tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
[/COLOR][COLOR=#0000bb]GO
[/COLOR][color=#007700]IF [/color][color=#0000bb]EXISTS [/color][color=#007700]([/color][color=#0000bb]SELECT [/color][color=#007700]* [/color][color=#0000bb]FROM [/color][COLOR=#ff8000]#tmpErrors) ROLLBACK TRANSACTION
[/COLOR][COLOR=#0000bb]GO
[/COLOR][color=#007700]IF @@[/color][color=#0000bb]TRANCOUNT[/color][color=#007700]>[/color][COLOR=#0000bb]0 BEGIN
[/COLOR][color=#007700]PRINT [/color][COLOR=#dd0000]'The database update succeeded'
[/COLOR][COLOR=#0000bb]COMMIT TRANSACTION
END
[/COLOR][color=#007700]ELSE PRINT [/color][COLOR=#dd0000]'The database update failed'
[/COLOR][COLOR=#0000bb]GO
DROP TABLE [/COLOR][COLOR=#ff8000]#tmpErrors
[/COLOR][COLOR=#0000bb]GO
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT[/COLOR][color=#007700], [/color][color=#0000bb]ANSI_PADDING[/color][color=#007700], [/color][color=#0000bb]ANSI_WARNINGS[/color][color=#007700], [/color][color=#0000bb]CONCAT_NULL_YIELDS_NULL[/color][color=#007700], [/color][color=#0000bb]ARITHABORT[/color][color=#007700], [/color][color=#0000bb]QUOTED_IDENTIFIER[/color][color=#007700], [/color][color=#0000bb]ANSI_NULLS[/color][color=#007700], [/color][COLOR=#0000bb]NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
[/COLOR][color=#007700]-- [/color][color=#0000bb]Pointer used [/color][color=#007700]for [/color][color=#0000bb]text [/color][color=#007700]/ [/color][color=#0000bb]image updates[/color][color=#007700]. [/color][color=#0000bb]This might not be needed[/color][color=#007700], [/color][color=#0000bb]but is declared here just in [/color][COLOR=#007700]case
DECLARE @[/COLOR][color=#0000bb]pv binary[/color][color=#007700]([/color][color=#0000bb]16[/color][COLOR=#007700])

[/COLOR][COLOR=#0000bb]BEGIN TRANSACTION

[/COLOR][color=#007700]-- [/color][color=#0000bb]Add 16 rows to [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][COLOR=#007700]]
[/COLOR][color=#0000bb]SET IDENTITY_INSERT [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] [/color][COLOR=#0000bb]ON
INSERT INTO [/COLOR][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]1[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Ahmed'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]2[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Ahmad'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Gamal'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]3[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Yusef'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]4[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Yousef'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]5[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Hani'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]6[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Hany'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]7[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Hanee'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]8[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Honey'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]9[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Mohammed'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]10[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Muhammed'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]11[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Mahammed'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]12[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Mahamed'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]13[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Mohamed'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Badr'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]14[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Ahmed'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'ElsSalous'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]15[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Ahmed'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Elkourdy'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]INSERT INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] ([[/color][color=#0000bb]ID[/color][color=#007700]], [[/color][color=#0000bb]FirstName[/color][color=#007700]], [[/color][color=#0000bb]LastName[/color][color=#007700]]) [/color][color=#0000bb]VALUES [/color][color=#007700]([/color][color=#0000bb]16[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Sameer'[/color][color=#007700], [/color][color=#0000bb]N[/color][color=#dd0000]'Abdelwahed'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]SET IDENTITY_INSERT [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]FriendsData[/color][color=#007700]] [/color][COLOR=#0000bb]OFF

COMMIT TRANSACTION
GO  
[/COLOR][/COLOR]



والله الموفق لكل خير



متغيب
الموقع البحث
}}}
تم الشكر بواسطة:
موضوع مغلق 


الردود في هذا الموضوع
معلومة --- البحث الصوتي مع الدالة --- Difference &amp; soundex --- مهم جداً - بواسطة Raggi Tech - 21-10-12, 05:51 PM

المواضيع المحتمل أن تكون متشابهة .
الموضوع : الكاتب الردود : المشاهدات : آخر رد
  [سؤال] البحث ضمن النصوص في SQL nizar haider 1 1,682 15-04-20, 05:56 PM
آخر رد: nizar haider
  معلومة ---- Unicode ---------- RaggiTech 0 2,496 21-10-12, 07:00 PM
آخر رد: RaggiTech
  معلومة --- دالة coalesce --- RaggiTech 0 3,640 21-10-12, 06:49 PM
آخر رد: RaggiTech
  معلومة --- القوادح Triggers RaggiTech 3 6,525 21-10-12, 06:48 PM
آخر رد: RaggiTech
  معلومة --- الأمر Truncate RaggiTech 0 2,387 21-10-12, 05:35 PM
آخر رد: RaggiTech
  معلومة --- اعلامك بريدياً بتوقف SQL Server خطيرة جداً RaggiTech 1 2,643 21-10-12, 05:16 PM
آخر رد: RaggiTech
  معلومة --- Word Count RaggiTech 1 2,589 21-10-12, 05:08 PM
آخر رد: RaggiTech
  معلومة --- Stored Procedure RaggiTech 3 4,528 21-10-12, 05:07 PM
آخر رد: RaggiTech
  معلومة --- SQL Server 2008 RaggiTech 0 2,396 21-10-12, 05:00 PM
آخر رد: RaggiTech
  معلومة --- Openrowset --- هامة للغاية RaggiTech 4 3,070 21-10-12, 04:53 PM
آخر رد: RaggiTech

التنقل السريع :


يقوم بقرائة الموضوع: