How the TRIM() function works in Mariadb?

The TRIM() function in MariaDB is used to remove leading and trailing spaces or specified characters from a string. It allows you to trim characters from the beginning, end, or both ends of a string.

Syntax

The syntax of the MariaDB TRIM() function has two variations:

TRIM([remstr FROM] str)
TRIM([remstr FROM] str FROM remstr)

• remstr (optional): This is the string containing the characters to be removed from the start and end of the input string str. If not specified, it defaults to removing leading and trailing spaces.
• str: This is the input string from which characters need to be trimmed.

The function returns a string with the specified characters removed from the start and end of the input string.

Examples

Example 1: Trimming leading and trailing spaces

This example demonstrates how to use the TRIM() function to remove leading and trailing spaces from a string.

SELECT TRIM('   Hello, World!   ') AS trimmed_string;

The following is the output:

+----------------+
| trimmed_string |
+----------------+
| Hello, World!  |
+----------------+

In this example, the TRIM() function removes the leading and trailing spaces from the input string ’ Hello, World! ‘, resulting in ‘Hello, World!’.

Example 2: Trimming specified characters

This example shows how to trim specific characters from a string using the TRIM() function.

SELECT TRIM('*' FROM '***Hello, World!***') AS trimmed_string;

The following is the output:

+----------------+
| trimmed_string |
+----------------+
| Hello, World!  |
+----------------+

In this example, the TRIM() function removes the asterisk characters (’*’) from the start and end of the input string ‘Hello, World!’, resulting in ‘Hello, World!’.

Example 3: Trimming from both ends

This example demonstrates how to trim characters from both ends of a string using the TRIM() function.

SELECT TRIM(BOTH '.' FROM '...Hello, World!...') AS trimmed_string;

The following is the output:

+----------------+
| trimmed_string |
+----------------+
| Hello, World!  |
+----------------+

In this example, the TRIM() function removes the period characters (’.’) from both the start and end of the input string ‘…Hello, World!…’, resulting in ‘Hello, World!’.

Example 4: Using TRIM() with a table

This example shows how to use the TRIM() function in combination with a table.

DROP TABLE IF EXISTS strings;
CREATE TABLE strings (
    id INT PRIMARY KEY,
    string_value VARCHAR(255)
);
INSERT INTO strings VALUES
    (1, '   Trimmed   '),
    (2, '**Trimmed**');

SELECT id, TRIM(string_value) AS trimmed_string
FROM strings;

The following is the output:

+----+----------------+
| id | trimmed_string |
+----+----------------+
|  1 | Trimmed        |
|  2 | **Trimmed**    |
+----+----------------+

In this example, the TRIM() function is used to remove leading and trailing spaces from the string_value column in the strings table, and the trimmed strings are included in the result set.

Example 5: Using TRIM() with other string functions

This example demonstrates how to use the TRIM() function in combination with other string functions.

SELECT TRIM(CONCAT('   ', UPPER('hello, world!'), '   ')) AS trimmed_string;

The following is the output:

+----------------+
| trimmed_string |
+----------------+
| HELLO, WORLD!  |
+----------------+

In this example, the CONCAT() function is used to concatenate a string with leading and trailing spaces to the uppercase version of ‘hello, world!’ (obtained using UPPER()). Then, the TRIM() function removes the leading and trailing spaces from the resulting string, giving the output ‘HELLO, WORLD!’.

Related Functions

The following are some functions related to the MariaDB TRIM() function:

• MariaDB LTRIM() function is used to remove leading spaces or specified characters from a string.
• MariaDB RTRIM() function is used to remove trailing spaces or specified characters from a string.
• MariaDB REPLACE() function is used to replace occurrences of a substring within a string with another substring.
• MariaDB LPAD() and RPAD() functions are used to left-pad or right-pad a string with specified characters, respectively.

Conclusion

The TRIM() function in MariaDB is a versatile tool for removing leading and trailing spaces or specified characters from strings. It can be used in various scenarios, such as data cleaning, text manipulation, and preprocessing. By understanding the usage and capabilities of this function, along with related string functions, developers can effectively work with and manipulate string data in their MariaDB applications.