1

I have database with table 'Product', column 'Name'. I need to implement case and accent insensitive search so I tried to change collation of the column like this:

EXEC sp_fulltext_column @tabname = 'Product' , @colname = 'Name', @action = 'drop' 

ALTER TABLE dbo.Product
ALTER COLUMN Name nvarchar(400) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL;

EXEC sp_fulltext_column @tabname = 'Product' , @colname = 'Name', @action = 'add'

Then I checked the collation of the column like this:

SELECT collation_name  FROM sys.columns 
WHERE name = 'name' AND object_id = (select object_id from sys.tables where name = 'Product');

And collation is set correctly to CI and AI (SQL_Latin1_General_CP1_CI_AI).

I have test products in database with names like 'Čučoriedka', 'Čúčo', 'Test'. I would like to be able select 'Čučoriedka' and 'Čúčo' by query:

select * from product where CONTAINS([Name], 'cuc')

But neither 'test' is working with this queries:

select * from product where CONTAINS([Name], 'tes')
select * from product where PATINDEX('tes', [Name] COLLATE SQL_Latin1_General_CP1_CI_AI ) > 0

I tried also another collations, but nothing works.

Do you have any idea what can help here?

Thanks a lot! Marek

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ezekiell
  • 19
  • 6
  • Maybe this will help you http://stackoverflow.com/questions/2461522/how-do-i-perform-an-accent-insensitive-compare-e-with-e-e-e-and-e-in-sql-ser – Teis Lindemark Apr 25 '14 at 06:28

1 Answers1

0

I found the solution! I found out that, collation has impact only for ordering. I'm using fulltext search so I need to change fulltext indexes. Here is how I did if anyone else need do the same.. 

IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id('Product'))
DROP FULLTEXT INDEX ON [Product]

DECLARE @create_index_text nvarchar(4000)
SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
  ALTER FULLTEXT INDEX ON [Product]([Name]  Language 1051, [ShortDescription]  Language 1051, [FullDescription]  Language 1051, [Sku]  Language 1051)
  KEY INDEX [' + dbo.[nop_getprimarykey_indexname] ('Product') +  '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
EXEC(@create_index_text)

ALTER FULLTEXT CATALOG nopCommerceFullTextCatalog REBUILD WITH ACCENT_SENSITIVITY=OFF;

The language code 1051 is for slovak language, you can find your code here: http://technet.microsoft.com/en-us/library/ms176076.aspx

Hope it helps!

Ezekiell
  • 19
  • 6
  • That's not right. Collation is used for string comparison as well. Your issue is not "case sensitivity", but "accent insensitivity (which the collation you've selected also has). You may want to try `where name like '%tes%'`, as you are actually using a computed comparison with PatINDEX. – Curt Apr 25 '14 at 23:13
  • In general, your solutions should work but I need solution wich works with fulltext indexes, therefor collation doeasn't fix my issue but specifing language for this indexes with setting accent sensitivity for all catalog did. Anywy thanks for help! – Ezekiell Apr 26 '14 at 08:53