WEIGHT_STRING

Syntax

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

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

Description

The WEIGHT_STRING() function returns the weight of the input string as a binary string, which represents the comparison and sorting value of the string. 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 in the sorting order).

The WEIGHT_STRING() function is primarily used for internal debugging and testing, especially when new collations are added.

Syntax

The syntax of the WEIGHT_STRING() function is described as follows:

Keyword	Description
str	The input string expression. If the input is a non-binary (character) string, such as a CHAR, VARCHAR, or TEXT value, the return value contains the collation weight of the string. If the input is a binary (byte) string, such as a BINARY, VARBINARY, or BLOB value, the return value is the same as the input (each byte in the binary string has a weight equal to its byte value). If the input is NULL, the return value is NULL. For non-NULL return values, if the length is within the maximum length of VARBINARY, the data type is VARBINARY; otherwise, the data type is BLOB.
AS	This clause converts the input string to the specified type and length, and is optional.
LEVEL	This clause specifies the weight level of the return value, and is optional.
flags	This parameter is not used and is optional.

AS Clause

The AS clause can be used to explicitly 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 less than the length of the input string, the string is truncated to N characters without any warning.

• AS BINARY(N) converts the string to a binary string, where N is the number of bytes (not characters) and is padded with 0x00 bytes (not spaces).

Here is an example:

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

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

LEVEL Clause

The LEVEL clause can be used to specify the weight level of the return value. Multiple levels can be separated by commas (,) to form a list, and a range of consecutive levels 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 level of the input string is treated as the maximum collation level. The maximum collation level varies depending on the collation, but it will never exceed 6.

• The 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 number (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 collation level.

• If the LEVEL clause uses a list notation (instead of a 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 weights in reverse order (i.e., reverses the string, placing the first character at the end and the last character at the beginning).

Here is an example:

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

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

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

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

Example

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

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

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