How the WEIGHT_STRING() function works in Mariadb?

The WEIGHT_STRING() function in MariaDB is used to return the weight string of a string.

Posted on

The WEIGHT_STRING() function in MariaDB is used to return the weight string of a string. This function is primarily used for comparing strings.

Syntax

The syntax for the MariaDB WEIGHT_STRING() function is as follows:

WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [LEVEL {1|2}])

Where str is the string to be weighted, N is the number of characters to return, and LEVEL specifies the level of weighting.

Examples

Example 1: Basic Usage of WEIGHT_STRING()

This example shows how to get the weight string of a specific string.

SELECT WEIGHT_STRING('MariaDB') AS weight_string;

Below is the output for the statement:

+----------------+
| weight_string  |
+----------------+
|  M A R I A D B |
+----------------+

This output represents the weight string of ‘MariaDB’.

Example 2: Using WEIGHT_STRING() with a Table

First, let’s create a table with some strings and then use the WEIGHT_STRING() function to find out their weights.

DROP TABLE IF EXISTS example_strings;
CREATE TABLE example_strings (string_value VARCHAR(100));
INSERT INTO example_strings VALUES ('Hello'), ('World'), ('MariaDB');

Now, let’s query the table:

SELECT string_value, WEIGHT_STRING(string_value) AS weight_string FROM example_strings;

Below is the output for the statement:

+--------------+----------------+
| string_value | weight_string  |
+--------------+----------------+
| Hello        |  H E L L O     |
| World        |  W O R L D     |
| MariaDB      |  M A R I A D B |
+--------------+----------------+

The output shows the weight string for each value in the table.

Example 3: Specifying the Number of Characters

To return a specific number of characters in the weight string:

SELECT WEIGHT_STRING('MariaDB' AS CHAR(4)) AS weight_string;

The output will show the weight string for the first four characters of ‘MariaDB’.

+---------------+
| weight_string |
+---------------+
|  M A R I      |
+---------------+

Example 4: WEIGHT_STRING() with Different Levels

You can specify the level of weighting to get different weight strings.

SELECT WEIGHT_STRING('MariaDB' LEVEL 1) AS level_1_weight,
       WEIGHT_STRING('MariaDB' LEVEL 2) AS level_2_weight;

This will return two different weight strings based on the specified levels.

+----------------+----------------+
| level_1_weight | level_2_weight |
+----------------+----------------+
|  M A R I A D B |  M A R I A D B |
+----------------+----------------+

Example 5: WEIGHT_STRING() in a WHERE Clause

Using the WEIGHT_STRING() function in a WHERE clause to filter results:

SELECT * FROM example_strings WHERE WEIGHT_STRING(string_value) = WEIGHT_STRING('Hello');

This will return rows where the string_value has the same weight string as ‘Hello’.

+--------------+
| string_value |
+--------------+
| Hello        |
+--------------+

Below are a few functions related to the MariaDB WEIGHT_STRING() function:

  • MariaDB CHAR() function is used to convert a numeric value to its corresponding ASCII character.
  • MariaDB ORD() function returns the character code for the leftmost character of the argument.
  • MariaDB SOUNDEX() function returns a phonetic representation of a string.

Conclusion

The WEIGHT_STRING() function is a powerful tool in MariaDB for string comparison and sorting based on weight strings. It is especially useful in collation and indexing where exact string matching is required. With the examples provided, you should be able to utilize this function effectively in your database operations.