CODEWITHSUNDEEP
sqlsql-server-2005collation
David Brunelle
David Brunelle

Reputation: 6450

Modify search to make it Accent Insensitive in SQL Server

I have a database which current collation is French_CI_AS, which means that searches are case insensitive, but accent sensitive. I had the impression that changing the collation of the database to French_CI_AI would solve my problem so I did it. However, I still can't make a simple search work Accent-Insensitive. I check and the table collation is French_CI_AI, which is probably because it was changed along with the database.

Is there some way to make the search possible with Accent insensitive on all database ? Or is there any way to make a single seach that way ? I only have one or two Stored procedures that would need that so I could go that way to.

In case it makes any differences, the datatype I look for are all nvarchar(n)s.

Thanks

Upvotes: 3

Views: 4117

Answers (2)

jboo
jboo

Reputation: 374

First, chaging the database collation does not impact existing column in table but will affect newly created table and column. It's the default collation to use when creating new object in the database.

So, what you have to do is to find all existing column and updating current collation.

You can do it with a sql cursor and query all table and column and update collation for specific type or column name.

By example :

1) you change the collation for future object

USE master;
GO
ALTER DATABASE databasename COLLATE French_CI_AI ;
GO

2) change manually the column collation for specific column

 ALTER TABLE tablenameX ALTER COLUMN LastName varchar(100) COLLATE French_CI_AI NULL
 ALTER TABLE tablenameY ALTER COLUMN FirstName varchar(100) COLLATE French_CI_AI NULL

3) generate a sql script with cursor

You need to decide what column to change collation. It can be by type, name or specific table.

You can start by looking wich type of caracter type you have and replace them in the example, see the column cursor.

SELECT distinct DATA_TYPE from information_schema.columns

you can refer to this post :

http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

declare @CollationName as nvarchar = 'Latin1_General_CI_AS'

declare @tablename as nvarchar(100) = ''
declare @sqltext as nvarchar(100) = ''
declare @columnname as nvarchar(200) = ''
declare @DataType as nvarchar(100) = ''
declare @CharacterMaxLen as int = 0
declare @IsNullable as bit = 0

DECLARE MyTableCursor Cursor
FOR 
SELECT * from information_schema.TABLES
            WHERE TABLE_TYPE = 'BASE TABLE'
OPEN MyTableCursor
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE MyColumnCursor Cursor
        FOR 
        SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE from information_schema.columns
            WHERE table_name = @TableName AND  (Data_Type LIKE '%char%' 
            OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
            ORDER BY ordinal_position 
        Open MyColumnCursor

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        WHILE @@FETCH_STATUS = 0
            BEGIN
            SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' + 
              @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END + 
              ') COLLATE ' + @CollationName + ' ' + 
              CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
            PRINT @SQLText 

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        END
        CLOSE MyColumnCursor
        DEALLOCATE MyColumnCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

You can make the search use whatever collation you want, e.g.

WHERE column COLLATE FRENCH_CI_AI LIKE '%something%' COLLATE FRENCH_CI_AI

However I suspect it will work better if you actually fix the column (which didn't happen when you changed the database collation). Leaving out any constraints and other dependencies, the short answer of how to fix this:

ALTER TABLE dbo.foo ADD newcol NVARCHAR(32) COLLATE FRENCH_CI_AI;

UPDATE dbo.foo SET newcol = oldcol;

ALTER TABLE dbo.foo DROP COLUMN oldcol;

EXEC sp_rename N'dbo.foo.newcol', N'oldcol', 'COLUMN';

Upvotes: 4

Related Questions