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...

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...

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)...

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...

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...

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...

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...

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...

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....

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 ....

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!

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...

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...