logo
down
shadow

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


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

By : user2176324
Date : October 14 2020, 02:21 PM
like below fixes the issue 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).
code :
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;


Share : facebook icon twitter icon
How can I select columns with specific collation from mysql?

How can I select columns with specific collation from mysql?


By : user1952849
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further SHOW FULL COLUMNS is what you need:
code :
SHOW FULL COLUMNS FROM xxx WHERE COLLATION = 'utf8_general_ci'
how we can select two columns having different collation

how we can select two columns having different collation


By : Shamna Skor
Date : March 29 2020, 07:55 AM
help you fix your problem Is the error a difference in case sensitivity between the two tables? That is the error that I have most often seen.
If so, collate the offending table back to good old Latin1_General_CI_AS or whatever else is most appropriate.
code :
SELECT Col1 COLLATE Latin1_General_CI_AS, 
       Col2 COLLATE Latin1_General_CI_AS FROM Table1
UNION ALL 
SELECT Col1, Col2 FROM Table2
How to change collation of a linked server or collation of a table

How to change collation of a linked server or collation of a table


By : user2987515
Date : March 29 2020, 07:55 AM
will be helpful for those in need You can change the collation for the purposes of the join. The syntax would be something like this:
code :
Select *
From   TableA
       Inner Join TableB
         On TableA.StringColumn = TableB.StringColumn Collate Collation_Name_Here
Refreshing SYS.columns after db collation change

Refreshing SYS.columns after db collation change


By : Maryke
Date : March 29 2020, 07:55 AM
it should still fix some issue Altering the database collation doesn't change the existing columns. You need to ALTER TABLE
code :
ALTER TABLE Employees
  ALTER COLUMN FirstName VARCHAR(20) COLLATE  Latin1_General_CI_AI
SQL Server change collation on varchar columns that has a foreign key constraint

SQL Server change collation on varchar columns that has a foreign key constraint


By : Irasya
Date : March 29 2020, 07:55 AM
I wish this help you Well we cannot change collation of column which are referenced. It is clearly mentioned in this link https://msdn.microsoft.com/en-us/library/ms190920.aspx.
As far as automate dropping and creating question is concerned we can do that.
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org