How to Set Collation of a Column with SQL

How to set collation of a column with SQL?

To change the database's collation

ALTER DATABASE MyDataBase COLLATE [NewCollation]

To change the collation of a column

ALTER TABLE MyTable ALTER COLUMN Column1 [TYPE] COLLATE [NewCollation]

But there are a number of limitations on when you can do this, very notably that this is denied if the column is used in any index.

You can do more in SSMS in some cases.

The syntax docs list the restrictions:

• https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017
• https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017

Collate column to SQL default collation

As @Serg said you can use dynamic sql. Try the following code:

SELECT  [name], [collation_name] 
FROM    sys.columns 
WHERE   [object_id] = OBJECT_ID( N'x.dbo.x', N'U') /*table name*/
    AND [name] = 'name'                            /*column name*/

DECLARE @sql nvarchar(max)
,       @ServerCollation nvarchar(100)

SELECT @ServerCollation = CAST( SERVERPROPERTY( N'Collation') AS nvarchar(max))
SELECT @sql = 'ALTER TABLE dbo.x ALTER COLUMN [name] nvarchar(max) COLLATE ' + @ServerCollation
EXECUTE sp_executesql @sql

SELECT  [name], [collation_name] 
FROM    sys.columns 
WHERE   [object_id] = OBJECT_ID( N'x.dbo.x', N'U') 
    AND [name] = 'name'

Change the collation of a sql view

You must apply the COLLATE operator to each column in the SELECT clause.

As an exemple...

With the view :

CREATE VIEW V_CUST
AS
SELECT CustID, CustName, CustCity, CustDate
FROM   Customer
GO

You need to re-create the view as :

CREATE VIEW V_CUST
AS
SELECT CustID, CustName COLLATE French_BIN, CustCity  COLLATE French_BIN, CustDate
FROM   Customer
GO

SQL Server: How to change collation of all columns of my select on the fly

There's no way to change the collation of all text columns on the fly -- but manipulating the query to achieve the same result is doable. Martin's answer shows one way to do it, here's another using the metadata of the query. As a benefit, it's cursorless, and it avoids gratuitously introducing "new" columns if the collation is already what we want. It requires at least SQL Server 2012 to have sys.dm_exec_describe_first_result_set, and SQL Server 2017 to have STRING_AGG (on previous versions, the classic tricks for string concatenation can be used, like the FOR XML PATH trick).

DECLARE @query NVARCHAR(MAX) = N'SELECT * FROM MyTable';
DECLARE @tweakedQuery NVARCHAR(MAX);
DECLARE @tweakedCollation SYSNAME = N'Latin1_General_CI_AS';

SELECT @tweakedQuery = CONCAT(
    'SELECT ', 
    STRING_AGG(
        CONCAT(
            CONVERT(NVARCHAR(MAX), QUOTENAME([name])), 
            CASE 
                WHEN collation_name IS NOT NULL AND collation_name <> @tweakedCollation
                THEN ' COLLATE ' + @tweakedCollation + ' AS ' + QUOTENAME([name]) 
            END
        ), 
        ', ' + CHAR(13) + CHAR(10)
    ),
    ' FROM (' + CHAR(13) + CHAR(10),
    @query,
    CHAR(13) + CHAR(10) + ') _'
)
FROM sys.dm_exec_describe_first_result_set(@query, NULL, NULL);
PRINT @tweakedQuery;

Incorporating custom aliases would be simple, if they're generated systematically (based on the table or column name). Otherwise you'll have to patch things up manually.

For a one-off query (not something you necessarily need to do on the fly) it's still a much better idea to familiarize yourself with using regexes in your favorite editor. If your favorite editor has no regexes, get one that does.

SQL Server: changing collation of selected columns

Right-click on table name and choose Design. Then select column and go to Collation in column properties tab (inside Table Designer group).

Collate setting on a column in SQL Server table not working

You must ensure that the data is passed all the way to SQL Server using a format with compatible code points. Since you don't have Hebrew as your database or instance collation a varchar variable can't be used to store the data. So this

declare @d varchar(100) = 'שלום לך עולם' collate Hebrew_CI_AI 
select @d

outputs

???? ?? ????

In this scenario you have to pass the value to the databse as NVARCHAR

declare @d nvarchar(100) = N'שלום לך עולם' collate Hebrew_CI_AI 
select @d

You could use a varchar column with a Hebrew collation to store the data, but you should just use an nvarchar column. Still use the collation to produce the desired sorting and comparision semantics.

Collation change accent sensitive column

Off course you can...

1. modify one column of your table

2. use the COLLATE operator in the query to retrieve data with a CI/CS or AI/AS (or much more)

   ALTER TABLE <table_name>
   ALTER COLUMN <columns_name> <data_type> COLLATE collation_name

or

SELECT *
FROM   <table_name>
WHERE  <columns_name> COLLATE Latin1_General_CI_AI = 'Système'

How do you set collation for an entire query in SQL Server in order to do a case insensitive search?

It works if you put the collate statement before the IN clause:

WHERE Column COLLATE SQL_Latin1_General_CP1_CI_AS IN 
      ( 
        <cfqueryparam value="TEST1,TEST2,TEST3" 
             cfsqltype="cf_sql_varchar"
             list="true">
      ) 

However, I think the bind variables may cause it to have an unintended side effect. When I re-ran the exact same query directly afterward - just without the COLLATE - it still performed a case insensitive search.

WHERE Column IN 
      ( 
        <cfqueryparam value="TEST1,TEST2,TEST3" 
             cfsqltype="cf_sql_varchar"
             list="true">
      ) 

But change the query in some way, so the execution plan is not cached (like add AND 1 = 1 to the where clause) and the results are case sensitive again. Just something to keep in mind.

Update:

To avoid the caching issue, one possibility is to use a derived table. Then apply the collate statement to the SELECT list instead of the IN clause:

SELECT t.Column
FROM  (
        SELECT Column COLLATE SQL_Latin1_General_CP1_CI_AS AS Column
        FROM YourTable
      ) t 
WHERE t.Column IN 
      ( 
        <cfqueryparam value="TEST1,TEST2,TEST3" 
             cfsqltype="cf_sql_varchar"
             list="true">
      ) 

That said, if you always want to perform a case insensitive search on this column, it would be better to alter the column's collation as cfqueryparam suggested.

---

Related Topics