Accent insensitive search..
Holysmoke
I am trying to implement search functionality in my web application which
does not return the expected results in accent insensitive search.
Database collation : Latin1_General_CI_AI
If i create a index with italian language as word breaker and run the
following query, it returns results as expected.
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'Náboženský'
SELECT * FROM Schede
WHERE CONTAINS(Contents, @SearchWord)
but if @SearchWord ='Nabozensky', it returns nothing.
If create a index with english language as word breaker , it returns results
in both the cases.
Why accent insensitive search is not working , if i use italian language as
word breaker.
Please Help,
Thanks in advance,
Holy.
Russell Fields
in the following?
CREATE FULLTEXT CATALOG [Catalog_Name] WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]
http://msdn.microsoft.com/en-us/library/ms189520.aspx
RLF
"Holysmoke" <Holy...@discussions.microsoft.com> wrote in message
news:2F5179B4-637A-494A...@microsoft.com...
> Hi,
> I am trying to implement search functionality in my web application which
> does not return the expected results in accent insensitive search.
>
> Database collation : Latin1_General_CI_AI
>
> If i create a index with italian language as word breaker and run the
> following query, it returns results as expected.
>
> DECLARE @SearchWord nvarchar(30)
> SET @SearchWord = N'N�bozensk�'
Holysmoke
Yes, I have created the full-text catalog with ACCENT_SENSITIVITY =OFF.
It works perfect when i use english language as word breaker and i have this
accent insensitive search problem when i use the other languages(Italian) as
word breakers.
SQL SERVER 2005.
Contents='Náboženský smysl. Otevřít se nekonečnu,'
SELECT * FROM Schede WHERE CONTAINS(Contents, @SearchWord)
@SearchWord ='Náboženský' it returns result.
@SearchWord ='Naboženský' it returns result.
@SearchWord ='Nábozenský' it returns nothing.
I found that i have this problem with cyrillic characters.
Letter 'z' with diacritics.
Please help.
TIA,
Holy.
"Russell Fields" wrote:
> Does your full-text catalog have " WITH ACCENT_SENSITIVITY = OFF" as shown
> in the following?
>
> CREATE FULLTEXT CATALOG [Catalog_Name] WITH ACCENT_SENSITIVITY = OFF
> AUTHORIZATION [dbo]
>
> http://msdn.microsoft.com/en-us/library/ms189520.aspx
>
> RLF
>
>
> "Holysmoke" <Holy...@discussions.microsoft.com> wrote in message
> news:2F5179B4-637A-494A...@microsoft.com...
> > Hi,
> > I am trying to implement search functionality in my web application which
> > does not return the expected results in accent insensitive search.
> >
> > Database collation : Latin1_General_CI_AI
> >
> > If i create a index with italian language as word breaker and run the
> > following query, it returns results as expected.
> >
> > DECLARE @SearchWord nvarchar(30)
> > SET @SearchWord = N'Nábozenský'
> > SELECT * FROM Schede
> > WHERE CONTAINS(Contents, @SearchWord)
> >
> > but if @SearchWord ='Nabozensky', it returns nothing.
> >
> > If create a index with english language as word breaker , it returns
> > results
> > in both the cases.
> >
> > Why accent insensitive search is not working , if i use italian language
> > as
> > word breaker.
> >
> > Please Help,
> >
> > Thanks in advance,
> > Holy.
>
>
> .
>
Russell Fields
is an accent are two different things. (And the language wins the argument,
of course.) Apparently in the Italian language, the z is not considered to
be an accented-z, but is a totally different character. (But, as you
noticed, the English language considers it just another accent that can be
discarded.)
This is true of many languages that use accents. Furthermore, the rules are
changing over time, to the mixed delight/distress of users of those
languages.
Perhaps if this is a problem, you could try using the neutral word breaker,
but then you would lose stemming, inflectional, and so forth. (No guarantees
that you will like the results.)
RLF
"Holysmoke" <Holy...@discussions.microsoft.com> wrote in message
news:D8D4F5F3-602C-485D...@microsoft.com...
> Hi,
>
>
> Yes, I have created the full-text catalog with ACCENT_SENSITIVITY =OFF.
>
> It works perfect when i use english language as word breaker and i have
> this
> accent insensitive search problem when i use the other languages(Italian)
> as
> word breakers.
>
> SQL SERVER 2005.
>
> Contents='N�bozensk� smysl. Otevr�t se nekonecnu,'
>
> SELECT * FROM Schede WHERE CONTAINS(Contents, @SearchWord)
>
> @SearchWord ='N�bozensk�' it returns result.
>
> @SearchWord ='Nabozensk�' it returns result.
>
> @SearchWord ='N�bozensk�' it returns nothing.
>
> I found that i have this problem with cyrillic characters.
>
> Letter 'z' with diacritics.
>
> Please help.
>
> TIA,
> Holy.
> "Russell Fields" wrote:
>
>> Does your full-text catalog have " WITH ACCENT_SENSITIVITY = OFF" as
>> shown
>> in the following?
>>
>> CREATE FULLTEXT CATALOG [Catalog_Name] WITH ACCENT_SENSITIVITY = OFF
>> AUTHORIZATION [dbo]
>>
>> http://msdn.microsoft.com/en-us/library/ms189520.aspx
>>
>> RLF
>>
>>
>> "Holysmoke" <Holy...@discussions.microsoft.com> wrote in message
>> news:2F5179B4-637A-494A...@microsoft.com...
>> > Hi,
>> > I am trying to implement search functionality in my web application
>> > which
>> > does not return the expected results in accent insensitive search.
>> >
>> > Database collation : Latin1_General_CI_AI
>> >
>> > If i create a index with italian language as word breaker and run the
>> > following query, it returns results as expected.
>> >
>> > DECLARE @SearchWord nvarchar(30)
>> > SET @SearchWord = N'N�bozensk�'
Holysmoke
succeeds with accent insensitive search problem but ended up with the
apostrophe problem.
If i search for "Dall'Utopia" , the query returns
Dall'Utopia
Dalla Utopia
L'Utopia
in which it should return only "Dall'Utopia".
Is there any way that i can fix this by setting any properties in fulltext
index in SQL server 2005.
TIA,
Holy.
Only problem with the english / neutral word breaker ,
"Russell Fields" wrote:
> The problem is that what you think is an accent and what the language thinks
> is an accent are two different things. (And the language wins the argument,
> of course.) Apparently in the Italian language, the z is not considered to
> be an accented-z, but is a totally different character. (But, as you
> noticed, the English language considers it just another accent that can be
> discarded.)
>
> This is true of many languages that use accents. Furthermore, the rules are
> changing over time, to the mixed delight/distress of users of those
> languages.
>
> Perhaps if this is a problem, you could try using the neutral word breaker,
> but then you would lose stemming, inflectional, and so forth. (No guarantees
> that you will like the results.)
>
> RLF
>
>
>
> "Holysmoke" <Holy...@discussions.microsoft.com> wrote in message
> news:D8D4F5F3-602C-485D...@microsoft.com...
> > Hi,
> >
> >
> > Yes, I have created the full-text catalog with ACCENT_SENSITIVITY =OFF.
> >
> > It works perfect when i use english language as word breaker and i have
> > this
> > accent insensitive search problem when i use the other languages(Italian)
> > as
> > word breakers.
> >
> > SQL SERVER 2005.
> >
> > Contents='Nábozenský smysl. Otevrít se nekonecnu,'
> >
> > SELECT * FROM Schede WHERE CONTAINS(Contents, @SearchWord)
> >
> > @SearchWord ='Nábozenský' it returns result.
> >
> > @SearchWord ='Nabozenský' it returns result.
> >
> > @SearchWord ='Nábozenský' it returns nothing.
> >
> > I found that i have this problem with cyrillic characters.
> >
> > Letter 'z' with diacritics.
> >
> > Please help.
> >
> > TIA,
> > Holy.
> > "Russell Fields" wrote:
> >
> >> Does your full-text catalog have " WITH ACCENT_SENSITIVITY = OFF" as
> >> shown
> >> in the following?
> >>
> >> CREATE FULLTEXT CATALOG [Catalog_Name] WITH ACCENT_SENSITIVITY = OFF
> >> AUTHORIZATION [dbo]
> >>
> >> http://msdn.microsoft.com/en-us/library/ms189520.aspx
> >>
> >> RLF
> >>
> >>
> >> "Holysmoke" <Holy...@discussions.microsoft.com> wrote in message
> >> news:2F5179B4-637A-494A...@microsoft.com...
> >> > Hi,
> >> > I am trying to implement search functionality in my web application
> >> > which
> >> > does not return the expected results in accent insensitive search.
> >> >
> >> > Database collation : Latin1_General_CI_AI
> >> >
> >> > If i create a index with italian language as word breaker and run the
> >> > following query, it returns results as expected.
> >> >
> >> > DECLARE @SearchWord nvarchar(30)
> >> > SET @SearchWord = N'Nábozenský'
> >> > SELECT * FROM Schede
> >> > WHERE CONTAINS(Contents, @SearchWord)
> >> >
> >> > but if @SearchWord ='Nabozensky', it returns nothing.
> >> >
> >> > If create a index with english language as word breaker , it returns
> >> > results
> >> > in both the cases.
> >> >
> >> > Why accent insensitive search is not working , if i use italian
> >> > language
> >> > as
> >> > word breaker.
> >> >
> >> > Please Help,
> >> >
> >> > Thanks in advance,
> >> > Holy.
> >>
> >>
> >> .
> >>
>
>
> .
>
Russell Fields
between what a tool can do and what you want to have done. The
English/Neutral word breakers break on white spaces and punctuation,
interpreting the apostrophe as a non-indexed word breaking character. (And,
of course, to SQL Server the apostrophe also serves as the string delimiter
for quoted strings.)
I would have expected searching for the phrase "Dall Utopia" to find the
phrase "Dall'Utopia".
WHERE CONTAINS(Contents, ' "Dall Utopia" ')
I would need to see exactly how your query for this search is phrased. For
example, your result looks like what I would have expected if the query
were:
WHERE CONTAINS(Contents, ' "Dall" OR "Utopia" ')
Note: It is possible to manipulate the input and output to work around
this, but the workaround may be too onerous for you. You could change the
data for storing and fulltext indexing "Dall'Utopia" as
"DallAPOSTROPHEUtopia". Change the code to query that so that an embedded
apostrophe in the query also becomes "APOSTROPHE". Then, of course, you
will need to change the results back for display purposes so that your users
will see "Dall'Utopia". (I never do this.)
RLF
"Holysmoke" <Holy...@discussions.microsoft.com> wrote in message
news:C84B1460-3159-419E...@microsoft.com...
>> > Contents='N�bozensk� smysl. Otevr�t se nekonecnu,'
>> >
>> > SELECT * FROM Schede WHERE CONTAINS(Contents, @SearchWord)
>> >
>> > @SearchWord ='N�bozensk�' it returns result.
>> >
>> > @SearchWord ='Nabozensk�' it returns result.
>> >
>> > @SearchWord ='N�bozensk�' it returns nothing.
>> >
>> > I found that i have this problem with cyrillic characters.
>> >
>> > Letter 'z' with diacritics.
>> >
>> > Please help.
>> >
>> > TIA,
>> > Holy.
>> > "Russell Fields" wrote:
>> >
>> >> Does your full-text catalog have " WITH ACCENT_SENSITIVITY = OFF" as
>> >> shown
>> >> in the following?
>> >>
>> >> CREATE FULLTEXT CATALOG [Catalog_Name] WITH ACCENT_SENSITIVITY = OFF
>> >> AUTHORIZATION [dbo]
>> >>
>> >> http://msdn.microsoft.com/en-us/library/ms189520.aspx
>> >>
>> >> RLF
>> >>
>> >>
>> >> "Holysmoke" <Holy...@discussions.microsoft.com> wrote in message
>> >> news:2F5179B4-637A-494A...@microsoft.com...
>> >> > Hi,
>> >> > I am trying to implement search functionality in my web application
>> >> > which
>> >> > does not return the expected results in accent insensitive search.
>> >> >
>> >> > Database collation : Latin1_General_CI_AI
>> >> >
>> >> > If i create a index with italian language as word breaker and run
>> >> > the
>> >> > following query, it returns results as expected.
>> >> >
>> >> > DECLARE @SearchWord nvarchar(30)
>> >> > SET @SearchWord = N'N�bozensk�'
Holysmoke
DECLARE @SearchString AS NVARCHAR(100)
DECLARE @Sql AS NVARCHAR(400)
SET @SearchString ='dall''utopia'
SET @Sql =
'SELECT DISTINCT IDSCHEDA,CONTENTS
FROM
LET_SCHEDE_TEXT AS FT_TBL INNER JOIN
CONTAINSTABLE(LET_SCHEDE_TEXT, CONTENTS, ''"'+
Replace(@SearchString,'''','''''') + '"'')
AS KEY_TBL
ON FT_TBL.idcontatore = KEY_TBL.[KEY]'
EXECUTE(@Sql)
At the moment , i am manipulating in the ASP.Net page to filter those
records as a work around in which i wanted to avoid ..
TIA,
Holy.
"Russell Fields" wrote:
> >> > Contents='Nábozenský smysl. Otevrít se nekonecnu,'
> >> >
> >> > SELECT * FROM Schede WHERE CONTAINS(Contents, @SearchWord)
> >> >
> >> > @SearchWord ='Nábozenský' it returns result.
> >> >
> >> > @SearchWord ='Nabozenský' it returns result.
> >> >
> >> > @SearchWord ='Nábozenský' it returns nothing.
> >> >
> >> > I found that i have this problem with cyrillic characters.
> >> >
> >> > Letter 'z' with diacritics.
> >> >
> >> > Please help.
> >> >
> >> > TIA,
> >> > Holy.
> >> > "Russell Fields" wrote:
> >> >
> >> >> Does your full-text catalog have " WITH ACCENT_SENSITIVITY = OFF" as
> >> >> shown
> >> >> in the following?
> >> >>
> >> >> CREATE FULLTEXT CATALOG [Catalog_Name] WITH ACCENT_SENSITIVITY = OFF
> >> >> AUTHORIZATION [dbo]
> >> >>
> >> >> http://msdn.microsoft.com/en-us/library/ms189520.aspx
> >> >>
> >> >> RLF
> >> >>
> >> >>
> >> >> "Holysmoke" <Holy...@discussions.microsoft.com> wrote in message
> >> >> news:2F5179B4-637A-494A...@microsoft.com...
> >> >> > Hi,
> >> >> > I am trying to implement search functionality in my web application
> >> >> > which
> >> >> > does not return the expected results in accent insensitive search.
> >> >> >
> >> >> > Database collation : Latin1_General_CI_AI
> >> >> >
> >> >> > If i create a index with italian language as word breaker and run
> >> >> > the
> >> >> > following query, it returns results as expected.
> >> >> >
> >> >> > DECLARE @SearchWord nvarchar(30)
> >> >> > SET @SearchWord = N'Nábozenský'
> >> >> > SELECT * FROM Schede
> >> >> > WHERE CONTAINS(Contents, @SearchWord)
> >> >> >
> >> >> > but if @SearchWord ='Nabozensky', it returns nothing.
> >> >> >
> >> >> > If create a index with english language as word breaker , it returns
> >> >> > results
> >> >> > in both the cases.
> >> >> >
> >> >> > Why accent insensitive search is not working , if i use italian
> >> >> > language
> >> >> > as
> >> >> > word breaker.
> >> >> >
> >> >> > Please Help,
> >> >> >
> >> >> > Thanks in advance,
> >> >> > Holy.
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>
RS Emenu
Below link provide details description of case sensitive operation with LINQ
http://www.a2zmenu.com/MySql/Case-sensitive-search-in-SQL.aspx
> On Wednesday, January 27, 2010 9:00 AM Holysmoke wrote:
> Hi,
> I am trying to implement search functionality in my web application which
> does not return the expected results in accent insensitive search.
>
> Database collation : Latin1_General_CI_AI
>
> If i create a index with italian language as word breaker and run the
> following query, it returns results as expected.
>
> DECLARE @SearchWord nvarchar(30)
> SET @SearchWord = N'N??bo??ensk??'
> SELECT * FROM Schede
> WHERE CONTAINS(Contents, @SearchWord)
>
> but if @SearchWord ='Nabozensky', it returns nothing.
>
> If create a index with english language as word breaker , it returns results
> in both the cases.
>
> Why accent insensitive search is not working , if i use italian language as
> word breaker.
>
> Please Help,
>
> Thanks in advance,
> Holy.
>> On Wednesday, January 27, 2010 10:47 AM Russell Fields wrote:
>> Does your full-text catalog have " WITH ACCENT_SENSITIVITY = OFF" as shown
>> in the following?
>>
>> CREATE FULLTEXT CATALOG [Catalog_Name] WITH ACCENT_SENSITIVITY = OFF
>> AUTHORIZATION [dbo]
>>
>> http://msdn.microsoft.com/en-us/library/ms189520.aspx
>>
>> RLF
>>> On Thursday, January 28, 2010 9:58 AM Holysmoke wrote:
>>> Hi,
>>>
>>>
>>> Yes, I have created the full-text catalog with ACCENT_SENSITIVITY =OFF.
>>>
>>> It works perfect when i use english language as word breaker and i have this
>>> accent insensitive search problem when i use the other languages(Italian) as
>>> word breakers.
>>>
>>> SQL SERVER 2005.
>>>
>>> Contents='N??bo??ensk?? smysl. Otev????t se nekone??nu,'
>>>
>>> SELECT * FROM Schede WHERE CONTAINS(Contents, @SearchWord)
>>>
>>> @SearchWord ='N??bo??ensk??' it returns result.
>>>
>>> @SearchWord ='Nabo??ensk??' it returns result.
>>>
>>> @SearchWord ='N??bozensk??' it returns nothing.
>>>
>>> I found that i have this problem with cyrillic characters.
>>>
>>> Letter 'z' with diacritics.
>>>
>>> Please help.
>>>
>>> TIA,
>>> Holy.
>>> "Russell Fields" wrote:
>>>> On Tuesday, February 02, 2010 4:40 PM Russell Fields wrote:
>>>> The problem is that what you think is an accent and what the language thinks
>>>> is an accent are two different things. (And the language wins the argument,
>>>> of course.) Apparently in the Italian language, the z is not considered to
>>>> be an accented-z, but is a totally different character. (But, as you
>>>> noticed, the English language considers it just another accent that can be
>>>> discarded.)
>>>>
>>>> This is true of many languages that use accents. Furthermore, the rules are
>>>> changing over time, to the mixed delight/distress of users of those
>>>> languages.
>>>>
>>>> Perhaps if this is a problem, you could try using the neutral word breaker,
>>>> but then you would lose stemming, inflectional, and so forth. (No guarantees
>>>> that you will like the results.)
>>>>
>>>> RLF
>>>>> On Wednesday, February 03, 2010 10:23 AM Holysmoke wrote:
>>>>> Yes, i have tried with the english / neutral word breaker in which i
>>>>> succeeds with accent insensitive search problem but ended up with the
>>>>> apostrophe problem.
>>>>>
>>>>> If i search for "Dall'Utopia" , the query returns
>>>>>
>>>>> Dall'Utopia
>>>>> Dalla Utopia
>>>>> L'Utopia
>>>>>
>>>>> in which it should return only "Dall'Utopia".
>>>>>
>>>>> Is there any way that i can fix this by setting any properties in fulltext
>>>>> index in SQL server 2005.
>>>>>
>>>>> TIA,
>>>>> Holy.
>>>>>
>>>>>
>>>>>
>>>>> Only problem with the english / neutral word breaker ,
>>>>>
>>>>> "Russell Fields" wrote:
>>>>>> On Wednesday, February 03, 2010 11:51 AM Russell Fields wrote:
>>>>>> Language is very complicated. I am afraid that you are caught in the cracks
>>>>>> between what a tool can do and what you want to have done. The
>>>>>> English/Neutral word breakers break on white spaces and punctuation,
>>>>>> interpreting the apostrophe as a non-indexed word breaking character. (And,
>>>>>> of course, to SQL Server the apostrophe also serves as the string delimiter
>>>>>> for quoted strings.)
>>>>>>
>>>>>> I would have expected searching for the phrase "Dall Utopia" to find the
>>>>>> phrase "Dall'Utopia".
>>>>>> WHERE CONTAINS(Contents, ' "Dall Utopia" ')
>>>>>>
>>>>>> I would need to see exactly how your query for this search is phrased. For
>>>>>> example, your result looks like what I would have expected if the query
>>>>>> were:
>>>>>> WHERE CONTAINS(Contents, ' "Dall" OR "Utopia" ')
>>>>>>
>>>>>>
>>>>>> Note: It is possible to manipulate the input and output to work around
>>>>>> this, but the workaround may be too onerous for you. You could change the
>>>>>> data for storing and fulltext indexing "Dall'Utopia" as
>>>>>> "DallAPOSTROPHEUtopia". Change the code to query that so that an embedded
>>>>>> apostrophe in the query also becomes "APOSTROPHE". Then, of course, you
>>>>>> will need to change the results back for display purposes so that your users
>>>>>> will see "Dall'Utopia". (I never do this.)
>>>>>>
>>>>>> RLF
>>>>>>> On Friday, February 05, 2010 10:56 AM Holysmoke wrote:
>>>>>>> Here is the query , i use
>>>>>>>
>>>>>>>
>>>>>>> DECLARE @SearchString AS NVARCHAR(100)
>>>>>>> DECLARE @Sql AS NVARCHAR(400)
>>>>>>> SET @SearchString ='dall''utopia'
>>>>>>> SET @Sql =
>>>>>>> 'SELECT DISTINCT IDSCHEDA,CONTENTS
>>>>>>> FROM
>>>>>>> LET_SCHEDE_TEXT AS FT_TBL INNER JOIN
>>>>>>> CONTAINSTABLE(LET_SCHEDE_TEXT, CONTENTS, ''"'+
>>>>>>> Replace(@SearchString,'''','''''') + '"'')
>>>>>>> AS KEY_TBL
>>>>>>> ON FT_TBL.idcontatore = KEY_TBL.[KEY]'
>>>>>>>
>>>>>>> EXECUTE(@Sql)
>>>>>>>
>>>>>>> At the moment , i am manipulating in the ASP.Net page to filter those
>>>>>>> records as a work around in which i wanted to avoid ..
>>>>>>>
>>>>>>>
>>>>>>> TIA,
>>>>>>> Holy.
>>>>>>>
>>>>>>> "Russell Fields" wrote:
>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>> JustCode Visual Studio Development Add-In by Telerik
>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/d174b33a-d86e-48e7-b381-fcd1938b6775/justcode-visual-studio-development-addin-by-telerik.aspx