Fulltext and é character

  • 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

    http://www.chriskempster.com

    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"

  • 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()

  • 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

    http://www.chriskempster.com

    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"

  • 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