How the LPAD() function works in Mariadb?

The LPAD() function is a string function that returns a string that is left-padded with a specified character to a certain length. The LPAD() function is useful for formatting, aligning, or filling strings with a consistent length.

Syntax

The syntax of the LPAD() function is as follows:

LPAD(string, length, pad)

The string argument is the string to be left-padded. The length argument is an integer value that specifies the desired length of the returned string. The pad argument is the character or string to be used for padding. If the pad argument is empty or NULL, the function returns NULL.

The LPAD() function returns a string value that is the left-padded version of the string argument. If the length argument is positive, the function returns a string that is left-padded with the pad argument until it reaches the length argument. If the length argument is negative, the function returns NULL. If the length argument is zero, the function returns an empty string. If the length argument is less than the length of the string argument, the function returns a substring of the string argument that is equal to the length argument. If either the string, the length, or the pad argument is NULL, the function returns NULL.

Examples

Example 1: Basic usage of the LPAD() function

The following example shows how to use the LPAD() function with a simple string. It returns a string that is left-padded with the character ‘0’ to a length of 5.

SELECT LPAD('123', 5, '0');

The output is:

00123

This means that the LPAD() function adds two ‘0’ characters to the left of the string ‘123’ to make it 5 characters long.

Example 2: Using the LPAD() function with a negative length

The following example shows how to use the LPAD() function with a negative length. It returns a string that is right-padded with the character ‘*’ to a length of -5.

SELECT LPAD('abc', -5, '*');

The output is:

NULL

This means that the LPAD() function does not accept a negative length.

Example 3: Using the LPAD() function with a string pad

The following example shows how to use the LPAD() function with a string pad. It returns a string that is left-padded with the string ‘ab’ to a length of 8.

SELECT LPAD('xyz', 8, 'ab');

The output is:

ababaxyz

This means that the LPAD() function repeats the string ‘ab’ to the left of the string ‘xyz’ until it reaches the length of 8. Note that the last repetition of the pad string may be truncated to fit the desired length.

Related Functions

There are some other functions that are related to the LPAD() function in Mariadb. They are:

• RPAD(): This function returns a string that is right-padded with a specified character to a certain length. It is the opposite of the LPAD() function, meaning that RPAD(x, y, z) = LPAD(x, -y, z) for any string x, integer y, and string z.
• LTRIM(): This function returns a string that is left-trimmed of any leading spaces. It is similar to the LPAD() function, except that it removes characters instead of adding them.
• RTRIM(): This function returns a string that is right-trimmed of any trailing spaces. It is similar to the RPAD() function, except that it removes characters instead of adding them.
• TRIM(): This function returns a string that is trimmed of any leading or trailing spaces or a specified character. It is a combination of the LTRIM() and RTRIM() functions.

Here are some examples of using these related functions:

-- Get the right-padded version of '123' with '0' to a length of 5
SELECT RPAD('123', 5, '0');

-- Get the left-trimmed version of '   Hello'
SELECT LTRIM('   Hello');

-- Get the right-trimmed version of 'World   '
SELECT RTRIM('World   ');

-- Get the trimmed version of '  Hello World  '
SELECT TRIM('  Hello World  ');

Conclusion

In this article, we have learned how the LPAD() function works in Mariadb. We have seen its syntax, examples, and related functions. We have also learned how to use the LPAD() function to return a string that is left-padded with a specified character to a certain length. The LPAD() function is a useful function to format, align, or fill strings with a consistent length.