My goal is to sort a few numbers the same as a handful of characters.
ie:
4 sorts the same as A or a
3 sorts the same as E or e
Why isn't this working?
I've added the following to /usr/share/mysql/charsets/Index.xml
...
<charset name="utf8">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8_general_ci" id="33">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_bin" id="83">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_leet_ci" id="244">
<rules>
<reset>\u0E33</reset>
<t>\u0E2D</t>
<reset>\u0E8B</reset>
<t>\u0E2C</t>
<reset>\u0EC1</reset>
<t>\u0E2F</t>
<reset>\u0EFB</reset>
<t>\u0E2A</t>
<reset>\u0F82</reset>
<t>\u0E29</t>
<reset>\u0FEA</reset>
<t>\u0E2E</t>
<reset>\u1002</reset>
<t>\u0E30</t>
</rules>
</collation>
</charset>
...
I also tried as:
...
<collation name="utf8_leet_ci" id="244">
<rules>
<reset>A</reset>
<t>4</t>
<reset>E</reset>
<t>3</t>
<reset>G</reset>
<t>6</t>
<reset>I</reset>
<t>1</t>
<reset>O</reset>
<t>0</t>
<reset>S</reset>
<t>5</t>
<reset>T</reset>
<t>7</t>
</rules>
...
Here is the results of my testing:
CREATE TABLE `challenge_copy` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`category` varchar(250) COLLATE utf8_leet_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=141 DEFAULT CHARSET=utf8 COLLATE=utf8_leet_ci
SELECT category, HEX(category), HEX(WEIGHT_STRING(category))
FROM challenge_copy;
a 61 0E33
A 41 0E33
4 34 0E2D
e 65 0E8B
E 45 0E8B
3 33 0E2C
g 67 0EC1
G 47 0EC1
6 36 0E2F
i 69 0EFB
I 49 0EFB
1 31 0E2A
o 6F 0F82
O 4F 0F82
0 30 0E29
s 73 0FEA
S 53 0FEA
5 35 0E2E
t 74 1002
T 54 1002
7 37 0E30