MariaDB WEIGHT_STRING() Function

In MariaDB, WEIGHT_STRING() is a built-in function that returns a binary string representing the comparison and sort weights of the input strings.

If the input string is a non-binary value, the return value contains the string’s collation weight. If the input is a binary value, the result is the same as the input string. This is because the weight of each byte in the binary string is the byte value.

This function is a debug function for internal use. It can be used for testing and debugging collations.

MariaDB WEIGHT_STRING() Syntax

Here is the syntax of the MariaDB WEIGHT_STRING() function:

WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [LEVEL levels] [flags])
  levels: N [ASC|DESC|REVERSE] [, N [ASC|DESC|REVERSE]] ...

Parameters

str

Required. String to calculate weights.

AS {CHAR|BINARY}(N)

Optional. AS clause allows converting an input string to a binary or non-binary string and to a specific length.

  • AS CHAR(N) measures the length in characters and right pad with spaces to the desired length.
  • AS BINARY(N) measures the length in bytes rather than characters, and right pad 0x00 to the desired length.

The minimum value of N is 1, if it is less than the length of the input string, the string will be truncated without warning.

LEVEL levels

Optional. Specifies that the return value should contain the weight for the specific collation level.

The levels specifier can be a single integer, a comma-separated list of integers, or a dash-separated range of integers (spaces are ignored). Integers can range from 1 to max 6, depending on the collation, and need to be listed in ascending order.

  • If no LEVEL clause, the collation’s default 1 of maximum is assumed.
  • If specified without a scope LEVEL, the optional modifier is allowed.
  • ASC (Default) returns weights without any modification.
  • DESC returns the bitwise inverted weight.
  • REVERSE return weights in reverse order.

If you provide the wrong number of parameters, MariaDB will report an error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1.

Return value

In MariaDB, WEIGHT_STRING() is a built-in function that returns a binary string representing the comparison and sort weights of the input strings.

If the input string is a non-binary value, the return value contains the string’s collation weight. If the input is a binary string, the result is the same as the input string. This is because the weight of each byte in the binary string is the byte value.

MariaDB WEIGHT_STRING() Examples

Basic example

This statement shows the basic usage of the MariaDB WEIGHT_STRING() function:

SELECT HEX(WEIGHT_STRING('A'));

Output:

+-------------------------+
| HEX(WEIGHT_STRING('A')) |
+-------------------------+
| 0041                    |
+-------------------------+

Here, we use the HEX() function to represent the non-printable result in hexadecimal format.

AS clause

Below is an example using the AS clause to coerce an input string to a given type and length.

SET @str = 'a';
SELECT
  HEX(WEIGHT_STRING(@str AS CHAR(3))) 'Char 3',
  HEX(WEIGHT_STRING(@str AS CHAR(5))) 'Char 5',
  HEX(WEIGHT_STRING(@str AS BINARY(3))) 'Binary 3',
  HEX(WEIGHT_STRING(@str AS BINARY(5))) 'Binary 5'\G

Output:

  Char 3: 004100200020
  Char 5: 00410020002000200020
Binary 3: 610000
Binary 5: 6100000000

Collation

The following two examples demonstrate how strings can have different weight strings depending on the collation used.

The collation used in the first example is case-insensitive. The collation used in the second example is case sensitive.

SET @upper = _latin1 'HELLO' COLLATE latin1_general_ci;
SET @lower = lcase(@upper);
SELECT
  @upper 'String',
  HEX(@upper) 'Hex',
  HEX(WEIGHT_STRING(@upper)) 'Weight String'
UNION ALL
SELECT
  @lower,
  HEX(@lower),
  HEX(WEIGHT_STRING(@lower));

Output:

+--------+------------+---------------+
| String | Hex        | Weight String |
+--------+------------+---------------+
| HELLO  | 48454C4C4F | 695B797981    |
| hello  | 68656C6C6F | 695B797981    |
+--------+------------+---------------+

The second example is almost the same, except it uses a case-sensitive collation.

SET @upper = _latin1 'HELLO' COLLATE latin1_general_cs;
SET @lower = lcase(@upper);
SELECT
  @upper 'String',
  HEX(@upper) 'Hex',
  HEX(WEIGHT_STRING(@upper)) 'Weight String'
UNION ALL
SELECT
  @lower,
  HEX(@lower),
  HEX(WEIGHT_STRING(@lower));

Output:

+--------+------------+---------------+
| String | Hex        | Weight String |
+--------+------------+---------------+
| HELLO  | 48454C4C4F | 695B797981    |
| hello  | 68656C6C6F | 6A5C7A7A82    |
+--------+------------+---------------+

Conclusion

In MariaDB, WEIGHT_STRING() is a built-in function that returns a binary string representing the comparison and sort weights of the input strings.