FAQ Database Discussion Community

Highlighting Search Results: RegEx Character Collation?

When I run a fulltext MySQL query, thanks to Unicode character collations I will get results matching all of the following, whichever of them I may query for: saka, sakā, śāka, ṣaka etc. Where I'm stuck is with highlighting the matches in search results. With standard RegEx, I can only...

Why am I seeing “COLLATION 'xxx' is not valid for CHARACTER SET 'yyy'”

I am on MySQL 5.6.22 (InnoDB) on Amazon RDS. I have attempted to set all of my tables, columns, connection and database charset and collation settings to utf8mb4 / utf8mb4_unicode_ci. I can find no evidence anywhere that anything has charset latin1, yet when I execute the following code (either via...

Adding Collation to a SQL Server CTE statement

I am trying to determine how to add COLLATION to this operation.... WITH CTE (srtTxt, DuplicateCount) AS ( SELECT srtTxt, ROW_NUMBER() OVER(PARTITION BY srtTxt ORDER BY ID) AS DuplicateCount FROM dbo.tblLang **WHERE DuplicateCount > 1** ) DELETE FROM CTE WHERE DuplicateCount > 1 GO This is the setting I am...

Char encoding and SQL in C#

I have this sql: select productid from products where productcode = @code and @code is a parameter, it's value was ABCÊ but it matched ABC debugging it in visual studio showed � in the quickwatch. the database has Latin1_General_CI_AS as collation. The field type in the database is an nvarchar(50)...

MySQL select UTF-8 string with '=' but not with 'LIKE'

I have a table with some words that come from medieval books and have some accented letters that doesn't exists anymore in modern latin1 alphabet. I can represent these letters easily with UTF-8 combining characters. For example, to create a "J" with a tilde, I use the UTF-8 sequence \u004A+\u0303...

How to change the collation to make mysql case insensitive

I found out that when I query one of my tables it is case sensitive, so I tried to change the collation (I'm using Workbench in windows). I right clicked on the table -> alter table -> collation -> changed from utf8mb4_default to utf8mb4_general_ci But it didn't work and the...

SQL Duplicate query counter needing collation

I need some help coming up with a better query. I am trying to count and display the dups in a file based on a string but I need to apply collation so I can tell whether one word maybe capitalized and another not. For example 'exit' or 'Exit' are...

Is it ok to mix storage engines on same database?

I have an OpenCart project with about 50,000 products in the database. I've switched the web hosting from a VPS to a dedicated server and I've seen some improvements, but still the website is laggy. On the VPS all the tables were MyISAM with latin1_swedish_ci collation on mysql 5.1.x.x ....

Collations are not working fine in Solr

I am working on spell checking in Solr. I have implemented Suggestions and collations in my spell checker component. Most of the time collations work fine but in few case it fails. Working: I tried query:gone wthh thes wnd: In this wnd doesn't give suggestion wind but collation is coming...

Invalid object name error

I have two selects: select * from thr_prsnjob select * from tHR_PrsnJob First one is working ok. Second returns error: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.tHR_PrsnJob'. Database collation is: Collation=SQL_Croatian_CP1250_CI_AS I should get this error if database was case sensitive. If CI it should...

COLLATION is not valid for CHARACTER SET not corresponding to my settings

I have asked another question for my problem with collation setting, which seems to be ignored/overriden by some unwanted defaults. To workaround this, I want to use COLLATE in the queries returning "illegal mix of collations" error. However, when I try to, I get "COLLATION is not valid" error instead....

SQL Server: changing collation of selected columns

In SQL Server how to change a collation of only selected columns? Is there a way to do that using SQL Server Management Studio? I was googling around but what I found where instructions how to change collation of a database not a specific column. EDIT: So I found this...

phpmyadmin collation: latin and cyrillic

Could someone tell me please how to configure MySQL DB in phpmyadmin for storing both latin and cyrillic data sets in the same table? Thanks!