WEIGHT_STRING

Syntax

WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [LEVEL levels] [flags])

levels:
   N [ASC|DESC|REVERSE] [, N [ASC|DESC|REVERSE]] ...

Purpose

The WEIGHT_STRING() function returns the weight of the input string as a binary string, which represents the string's comparison and sorting value. The comparison rules are as follows:

• If WEIGHT_STRING(str1) = WEIGHT_STRING(str2), then str1 = str2 (i.e., str1 and str2 are equal).

• If WEIGHT_STRING(str1) < WEIGHT_STRING(str2), then str1 < str2 (i.e., str1 comes before str2).

The WEIGHT_STRING() function is for internal debugging purposes and can be used to test and debug collations, especially when adding new collations.

Syntax

The following table describes the syntax of the WEIGHT_STRING() function:

AS Clause

The AS clause can be used to forcibly convert the input string to a non-binary or binary string and set it to the specified length. The usage is as follows:

• AS CHAR(N) converts the string to a non-binary string and pads it with spaces on the right to a length of N (N>=1). If N is shorter than the input string, the string is truncated to N characters without any warning.

• AS BINARY(N) converts the string to a binary string, with N specified in bytes (not characters), and pads it with 0x00 bytes (not spaces).

Here is an example:

obclient> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 41422020                            |
+-------------------------------------+
1 row in set

obclient> SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));
+---------------------------------------+
| HEX(WEIGHT_STRING('ab' AS BINARY(4))) |
+---------------------------------------+
| 61620000                              |
+---------------------------------------+
1 row in set

LEVEL Clause

The LEVEL clause can be used to specify the collation weight level to include in the return value. Multiple levels can be separated by commas (,) to form a list, or a range can be specified using a hyphen (-). For example, LEVEL1-5.

The following rules generally apply:

• Any level less than 1 is treated as 1. Any level greater than the maximum collation value of the input string is treated as the maximum collation value. The maximum value varies by collation, but it will never exceed 6.

• Levels in the list must be in ascending order. In a range, if the second number is less than the first, it is treated as the same as the first (for example, 3-2 is equivalent to 3-3).

• If the LEVEL clause is omitted, it is assumed to be LEVEL 1 - max, where max is the maximum level of the collation.

• If the LEVEL clause uses list notation (instead of range notation), the following modifiers can be used after the level number:

  • ASC: returns the weight without any modification. This is the default.

  • DESC: returns the bit-reversed weight.

  • REVERSE: returns the weight in reverse order (i.e., reverses the string, with the first character at the end and the last character at the beginning).

Here is an example:

obclient> SELECT HEX(WEIGHT_STRING(0x56af LEVEL 1));
+------------------------------------+
| HEX(WEIGHT_STRING(0x56af LEVEL 1)) |
+------------------------------------+
| 56AF                               |
+------------------------------------+
1 row in set

obclient> SELECT HEX(WEIGHT_STRING(0x56af LEVEL 1 DESC));
+-----------------------------------------+
| HEX(WEIGHT_STRING(0x56af LEVEL 1 DESC)) |
+-----------------------------------------+
| A950                                    |
+-----------------------------------------+
1 row in set

obclient> SELECT HEX(WEIGHT_STRING(0x56af LEVEL 1 REVERSE ));
+---------------------------------------------+
| HEX(WEIGHT_STRING(0x56af LEVEL 1 REVERSE )) |
+---------------------------------------------+
| AF56                                        |
+---------------------------------------------+
1 row in set

obclient> SELECT HEX(WEIGHT_STRING(0x56af LEVEL 1 DESC REVERSE ));
+--------------------------------------------------+
| HEX(WEIGHT_STRING(0x56af LEVEL 1 DESC REVERSE )) |
+--------------------------------------------------+
| 50A9                                             |
+--------------------------------------------------+
1 row in set

Examples

Calculate the weight of the binary string "AB" and use the HEX() function to display the result of the WEIGHT_STRING() function.

obclient> SET @s = CAST('AB' AS BINARY);
Query OK, 0 rows affected

obclient> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 4142                   |
+------+---------+------------------------+
1 row in set