Re: how collation and character set works

[Rick James]

It is not a trivial undertaking to create a new collation. The online documents can probably tell you more than you can get from this forum.

It would be better to write remove the extra characters in Perl/PHP/Java code before inserting the strings into the table. You should probably create two columns -- one with the real text (this-is-a-text), one with the string to use for comparing (thisisatext) and index the latter one.

What do you mean by "8-bit"? "latin1" is an 8-bit code. But it is quite different from the 8-bit "utf8". They sort and compare differently. Perhaps "ascii" is the only 7-bit character set.

If all you want to do is to ignore '-', then the REPLACE function could be used:
select col from tbl where REPLACE(col, '-', '')='thisisatext';