October 16, 2003 at 1:03 am
Hi guys
This must be a standard problem, but cant seem to find much on it.
I have fulltext enabled on a simple table with this data:
row1. Limoncello Café
row2. My Cafe
My users want to type in "Cafe" and get both records. Using the contains clause and any of its ops I cant get this to work for me. I tried different word breakers, neutral, english etc etc with no luck. I have tried switching to unicode over varchar and again no luck. Of course, I can strip out e or é in a search and change the search of "Caf*" but its not nice.
Thoughts?
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
October 16, 2003 at 8:44 am
You need a sort order / collation that is accent-insensitive. During SQLServer setup it is a very easy option. In SQLServer 2000 you can also select a non-default collation in databases and columns.
See create table in BOL
You can see the definition of the possible collations with:
select * from ::fn_helpcollations()
October 22, 2003 at 8:37 pm
Hi there
Good thinking, i tried this at the column level:
CREATE TABLE [dbo].[cktest] (
[id] [int] NOT NULL ,
[col1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL
) ON [PRIMARY]
GO
going for the "AI" (accent insensitive option). Added the two rows mentioned, and repopulated in the index via a british english work breaker.
Again, no luck.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
October 23, 2003 at 7:14 am
I have to confess I never did this on columns level and I get a syntax error (Line 3: Incorrect syntax near 'COLLATE') for the create statement in a SQL_Latin1_General_CP1_CI_AS collation database on SQLServer 2000 SP3.
But when I created the table on a accent insensitive database (SQL_Latin1_General_CP1_CI_AI ) it worked fine and a select did not see the difference between cafe and café.
I guess the error does not say what it means and it is impossibe to make an accent insensitive column on a accent sensitive database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply