collation

MySQL: Get character-set of database or table or column?

by

Here’s how I’d do it – For Schemas (or Databases – they are synonyms): SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = “mydatabasename”; For Tables: SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = “mydatabasename” AND T.table_name = “tablename”; For Columns: SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = “mydatabasename” AND table_name … Read more

SQL Query Where Column = ” returning Emoji characters 🎃 and 🍰

by

This is collation dependant. Matches empty string SELECT 1 where N” = N’🍰’ COLLATE latin1_general_ci_as Doesn’t match empty string SELECT 1 WHERE N” = N’🍰’ COLLATE latin1_general_100_ci_as The 100 collations are more up-to-date (though still not bleeding edge, they have been available since 2008) and you should use more modern collations unless you have some … Read more

MySQL treats ÅÄÖ as AAO?

by

Yes, this is standard behaviour in the non-language-specific unicode collations. 9.1.13.1. Unicode Character Sets To further illustrate, the following equalities hold in both utf8_general_ci and utf8_unicode_ci (for the effect this has in comparisons or when doing searches, see Section 9.1.7.7, “Examples of the Effect of Collation”): Ä = A Ö = O Ü = U … Read more

How to explain sorting (numerical, lexicographical and collation) with examples to non technical testers?

by

Here are some explanations: Lexicographical In this case, you sort text without considering numbers. In fact, numbers are just “letters”, they have no numeric combined meaning. This means that the text “ABC123” is sorted as the letters A, B, C, 1, 2 and 3, not as A, B, C and then the number 123. This … Read more

Illegal mix of collations error in MySql

by

Here’s how to check which columns are the wrong collation: SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE collation_name=”latin1_general_ci” ORDER BY table_schema, table_name,ordinal_position; And here’s the query to fix it: ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE ‘latin1_swedish_ci’; Link

determining the character set of a table / database?

by

You can check the version using SELECT @@VERSION; It it’s 9.00 or greater, you can check the collation of a column using SELECT collation_name FROM sys.columns WHERE name=”column name” AND [object_id] = OBJECT_ID(‘dbo.table name’); And for the database using SELECT collation_name FROM sys.databases WHERE name=”database name”; If it’s < 9.0 then you’re using SQL Server … Read more

techhipbettruvabetnorabahisbahis forumu