WEIGHT_STRING|V4.2.1| docs|Distributed Database

WEIGHT_STRING

Last Updated：2023-12-25 08:49:42 Updated

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

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

Purpose

WEIGHT_STRING() returns the weight of an input string. The return value is a binary string that indicates the comparison and sorting value of the input string. The comparison rules are as follows:

If WEIGHT_STRING(str1) = WEIGHT_STRING(str2), str1 = str2, indicating that str1 is equal to str2.
If WEIGHT_STRING(str1) < WEIGHT_STRING(str2), str1 < str2, indicating that str1 ranks before str2.

The WEIGHT_STRING() function is used for internal debugging only. It can be used for testing and debugging sorting rules, especially when new sorting rules are added.

Parameters

The following table describes the parameters in the syntax of the WEIGHT_STRING() function.

Parameter	Description
str	The input string. If the input string is a non-binary string in characters, such as a value of the `CHAR`, `VARCHAR`, or `TEXT` data type, the return value contains the collation weight of the string. If the input string is a binary string in bytes, such as a value of the `BINARY`, `VARBINARY`, or `BLOB` data type, the return value is the same as the input. In a binary string, the weight of each byte is the byte value. If the input string is `NULL`, `NULL` is returned. For a non-NULL return value, if its length is within the valid length range of `VARBINARY`, the data type of this value is `VARBINARY`. Otherwise, the data type is `BLOB`.
AS	Converts the input string to the specified type and length. This clause is optional.
LEVEL	The weight level of the return value. This clause is optional.
flags	This parameter is optional and has not been used.

Considerations on the AS clause

The AS clause can forcibly convert the input string to a non-binary or binary string with the specified length.

AS CHAR(N) converts the string to a non-binary string and pads spaces to the right so that the string is N (N >= 1) characters long. If N is smaller than the length of the input string, the string is truncated to N characters without triggering an alert.
AS BINARY(N) converts the input string to a binary string. The unit of N is bytes rather than characters, and 0x00 bytes rather than spaces are padded.

Here are some examples:

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

Considerations on the LEVEL clause

The LEVEL clause specifies that the return value must contain the weight of the collation level. Multiple levels form a list and are separated with commas (,). In the case of multiple consecutive levels, the levels can be represented as a range, such as LEVEL1-5.

Note the following rules:

Any level value smaller than 1 is considered 1. Any level greater than the maximum value of the collation of the input string is considered the maximum value of the collation. The maximum value varies with the collation but will never be greater than 6.
The levels in the level list must be sorted in ascending order. In a level range, if the second number is smaller than the first number, it is considered to be the same as the first number. For example, 3-2 is equivalent to 3-3.
If the LEVEL clause is omitted, LEVEL 1 - max applies, where max is the maximum level in the collation.
If the LEVEL clause uses the list notation rather than the range notation, the following modifiers can be used behind the level number:
- ASC: returns the weights without making any modifications. This is the default setting.
- DESC: returns the bitwise-inverted weights.
- REVERSE: returns the weights in the reverse order, that is, reverses the string by putting the first character to the last and the last character to the first.