Syntax statement
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 an input string. The return value is a binary string representing the string's comparison and sorting values. The comparison rules are as follows:
If
WEIGHT_STRING(str1) = WEIGHT_STRING(str2), thenstr1 = str2(i.e.,str1andstr2are equal).If
WEIGHT_STRING(str1) < WEIGHT_STRING(str2), thenstr1 < str2(that is,str1precedesstr2).
The WEIGHT_STRING() function is for internal debugging only. It can be used to test and debug collations, especially when adding new collations.
Syntax
The syntax of the WEIGHT_STRING() function is as follows:
keyword |
Description |
|---|---|
| str | The input string expression.
NULL return value, if its length is within the maximum length range of VARBINARY, then its data type is VARBINARY, otherwise the data type is BLOB. |
| AS | This clause is optional and converts the input string to the specified type and length. |
| LEVEL | This clause specifies the weight level of the return value and is optional. |
| flags | This parameter is not currently used and is optional. |
Usage of the AS clause
The AS clause can force an input string to be converted into a non-binary or binary string and set it to a 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 ofN(where N ≥ 1). If N is less than the length of the input string, the string is truncated toNcharacters without any warning.AS BINARY(N)converts a string to a binary string.Nspecifies the number of bytes (not characters) to use for padding, and0x00bytes (not spaces) are used for padding.
For 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
Usage of the LEVEL clause
The LEVEL clause can be used to specify that the return value should include weights for the sorting rule levels. Multiple levels can be separated by commas (,) to form a list, or consecutive levels can be denoted by a hyphen (-) to define a range. For example, LEVEL1-5.
Generally, the following rules apply:
Any level less than 1 is treated as 1. Any level greater than the maximum value of the input string's collation is treated as the collation's maximum value. The maximum value varies by collation but is never greater than 6.
Levels in the level list must be arranged in ascending order. Within a level range, if the second number is less than the first number, it is considered equivalent to the first number (for example,
3-2is equivalent to3-3).If the
LEVELclause is omitted,LEVEL 1 - maxis assumed, wheremaxis the maximum level of the sorting rule.If the
LEVELclause uses list notation (rather than range notation), you can use the following modifiers after the level number:ASC: Returns the weights without making any modifications. This is the default setting.DESC: Returns the bitwise-reversed weight.REVERSE: Returns the weights in reverse order (that is, reverses the string, with the first character placed last and the last character placed first).
An example is as follows:
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 HEX() to display the result of WEIGHT_STRING().
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