In MariaDB, numerous built-in functions can be used to manipulate the string data. Using these functions, we can format the data, extract specific characters, or use search expressions.

1. CONCAT() Function

Syntax:

CONCAT(string_1, string_2);

This function returns a string that is the concatenation of the arguments provided. We can pass one or more arguments.
We can pass binary as well as non-binary strings.
If all arguments provided are non-binary, the resulting string will be non-binary, and if the arguments include any binary strings, the resulting string is a binary string.
If we are passing a numeric argument, it is converted to its equivalent binary string form. We can avoid this by explicitly typecasting as follows:

SELECT CONCAT(CAST(integer_column AS CHAR), char_column);

If we pass any argument as NULL function returns NULL.

The NULL argument hides all other information we are passing through other arguments from the result. If we do not want this, we can use the following:

CONCAT_WS () function: With the empty separator as this function is NULL safe.

IFNUL ()- This turns NULL parameter into empty strings.

Let's see examples:

MariaDB [student]> select CONCAT('Data', 'base');

We can see the result is both strings attached.

With NULL value:

MariaDB [student]> select CONCAT ('Data', NULL, 'base');

Here we can see result is NULL as one of the argument is NULL.

Using IFNULL() to handle NULLs:

MariaDB [student]> select concat('this is example!', IFNULL(@e,''));

2. LENGTH() Function:

Syntax:

LENGTH(string_1);

This function returns the length of the string string_1.
If the argument is not a string value, then it is converted into a string.
If the argument passed is NULL, the result is NULL.

Lets see example:

MariaDB [student]> select length('Database');

3. LENGTHB() Function:

Syntax:

LENGTHB(string_1);

This function returns the length of the string string_1 in terms of bytes. If the Oracle mode is not set, this function is equivalent to the LENGTH() function.
A multi-byte character is counted as multiple bytes, i.e., if we pass five two-byte characters, this function will return 10, whereas CHAR_LENGHT() will return 5.
If the argument is not a string value, then it is converted into a string.
If the argument passed is NULL, the result is NULL.

Lets see an example:

MariaDB [student]> select lengthb('Database');

4. ASCII() Function:

Syntax:

ASCII(string_1)

This function returns the numeric ASCII value of the leftmost character of the provided string argument.
If we pass NULL or an empty string, it will return 0.
If the argument passed is a single character, it will return the number code of that character.

Let's see an example:

1. For number:

MariaDB [student]> SELECT ASCII(9);

1. For number within quotes:

MariaDB [student]> SELECT ASCII('9');

2. For capital letter:

MariaDB [student]> SELECT ASCII('A');

3. For small letter:

MariaDB [student]> SELECT ASCII('a');

4. For string:

MariaDB [student]> SELECT ASCII('An aeroplane');

5. CONCAT_WS () Function

Syntax:

CONCAT_WS (separator, str_1, str_2, …str_n);

This function concatenates two or more expressions, and it adds a separator between the strings provided.
If we pass NULL, this function skips that string.
If the separator is NULL, then the function will return NULL.

Let's see an example:

MariaDB [student]> SELECT CONCAT_WS('_','The','MariaDB','Database');

Let's see with another separator.

MariaDB [student]> SELECT CONCAT_WS('123','The','MariaDB','Database');

Next with the NULL as parameter:

MariaDB [(none)]> SELECT CONCAT_WS('_','The','MariaDB',NULL,'Database');

We can see NULL is skipped and remaining string is appended.

MariaDB [(none)]> SELECT CONCAT_WS(NULL,'The','MariaDB','Database');

Here NULL is returned because we have passed NULL as separator.

6. FIELD() Function :

Syntax:

FIELD (pattern, string_1 [,string_2,…]);

This function will return the index position of the string or the number matching the provided pattern.
If none of the arguments don't match the pattern, it returns 0.
If we pass all arguments as strings, then they are treated as case-insensitive.
And if all arguments are numbers, they are treated as numbers; otherwise, they are treated as doubles.
If the given pattern matches more than once in the string, then the index of the first occurrence of patterned is returned.
If the pattern provided is NULL, the function will return 0.
This function is the complement of the ELT() function.

Let's see an example:

MariaDB [(none)]> SELECT FIELD('MariaDB','The','MariaDB','Database');

Here it returns 2 because MariaDB appears at second position.

MariaDB [(none)]> SELECT FIELD(4,2,6,8,10,4);

MariaDB [(none)]> SELECT FIELD('M','D','K','I') AS RESULT;

No match found.

MariaDB [(none)]> SELECT FIELD('NULL','D','K','I') AS RESULT;

Here we can see because of NULL as pattern result is 0.

MariaDB [(none)]> SELECT FIELD('ONE') AS RESULT;

It returns an error if we pass an incorrect number of arguments.

7. FORMAT() Function

Syntax:

FORMAT(no , decimal_places[, locale);

This function will format the given number to display a string, add a separator at the appropriate position, and round the result to the given decimal position. For example, 18988.321 will be formatted to 18,988.321
If we provide decimal places as 0, this function will return a string with no decimal places.

Lets see example:

MariaDB [(none)]> SELECT FORMAT(1923.99,2);

MariaDB [(none)]> SELECT FORMAT(1923.99,4);

Here we can see extra 0's are added to return the required format.

MariaDB [(none)]> SELECT FORMAT(1923.99,0) AS RESULT;

MariaDB [(none)]> SELECT FORMAT(1923.99,0,'rm_CH') AS RESULT;

Here we have used locale as 'rm_CH', which means format in Romansh language.

8. ELT() Function

Syntax:

ELT(N, stirng_1[,string_2, …..string_n]);

This function takes a numeric value and a series of strings as arguments and returns a string corresponding to the given number position. If the numeric value passed is a FLOAT, then it is rounded to the nearest INTEGER.
If we pass a numeric value less than 1 or greater number than a total number of strings or no number, this function returns NULL.
This function must have two arguments atleast.

Lets see example which will explain the functioning of function

MariaDB [(none)]> SELECT ELT(2, 'we','wew','fgf','gfg','kkk','mmm');

Here 'wew' is at 2nd position.

MariaDB [(none)]> SELECT ELT( 'we','wew','fgf','gfg','kkk','mmm');

No numeric value is passed so it returns NULL.

MariaDB [(none)]> SELECT ELT(7, 'we','wew','fgf','gfg','kkk','mmm');

Here numeric value greater than number of strings passed.

9. INSERT() Function:

Syntax:

INSERT(string_s, pos, len, new_str);

This function will return the string string_s with the substring at position pos and len characters long replaced by the string new_string.
If the pos provided is greater than the length of the string, the original string is returned.
If len is greater than the length of string, then the rest of the string is replaced from the position pos.
If any of the arguments passed is the NULL result is NULL.

Let's see an example:

MariaDB [(none)]> SELECT INSERT ('Database', 5,3,'INFO');

Here from 5th position 3 letters are replaced with new string a length provided is 3.

MariaDB [(none)]> SELECT INSERT ('Database', -1,3,'INFO');

Original string is returned as the pos provided is invalid.

MariaDB [(none)]> SELECT INSERT ('Database', 4,19,'INFO');

Here rest of the string from pos specified is replaced as the len provided exceeds the length of the original string.

10. INSTR Function

Syntax:

INSTR( str, substr);

This function will return the location of the first occurrence of the substring in the string str.
1 is the first position in the string.
While finding the location of the substring, the function does not perform a case-sensitive search.
If the substring mentioned is not found, the function will return 0.
If any argument is NULL, the result is NULL.

Let's see an example:

MariaDB [(none)]> SELECT INSTR('Database','base');

MariaDB [(none)]> SELECT INSTR('Database','BASE');

This function does case insensititve search so the result is same.

MariaDB [(none)]> SELECT INSTR('Database','in');

If no match it returns 0.

MariaDB [(none)]> SELECT INSTR('Database',4);

11. LCASE() Function:

Syntax:

LCASE(str)

This function converts all the characters present in the string to lowercase. If the characters present in the string are not letters, they are left unaffected.

By default, the LCASE function converts the characters using the current character mapping set that is latin1.

This function is equivalent to the LOWER function.

Lets see example:

MariaDB [(none)]> SELECT LCASE('THE DATABASE');

MariaDB [(none)]> SELECT LCASE('The Database');

12. LEFT() Function

Syntax:

LEFT(str, len)

This function returns the substring starting from the leftmost character of length len provided. If we mention a length that exceeds the size of the string, it returns the original string.

Let's see example:

MariaDB [(none)]> SELECT LEFT('Maria Database',5);

MariaDB [(none)]> SELECT LEFT('Maria Database',70);

Here if we pass len greater than length of string it returns whole string.

13. LOCATE() Function:

Syntax:

LOCATE(substr, str) 
OR
LOCATE(substr, str, pos)

The first syntax will return the position of the first occurrence of substring- substr in the string str.

And the second syntax returns the position of the first occurrence of substring-substr in the string str, starting at position pos. If the substring does not match, it returns 0.

If we pass any NULL argument, it returns NULL.

INSTR() is equivalent to this function but without the third parameter.

Lets see example:

MariaDB [(none)]> SELECT LOCATE('A','Maria Database');

As first a is at 2nd position

MariaDB [(none)]> SELECT LOCATE('A','Maria Database',7);

Here from 7th position the first occurring a is at 8.

MariaDB [(none)]> SELECT LOCATE('data','Maria Database');

Here we have passed a string to match.

14. LOWER() Function

All the working is same as LCASE()

Lets see example:

MariaDB [(none)]> SELECT LOWER('THE DATABASE');

MariaDB [(none)]> SELECT LOWER('The Database');

15. LPAD() Function

Syntax:

LPAD(str, len [,padhstr])

This function returns the string str, which is left-padded with the string padstr to a length of len characters. If the str has more characters than len, the return value is shortened to len characters. If we don't pass the parameter padstr , LPAD function pads spaces

Before MariaDB 10.3.1, the padstr argument was compulsory.

If we pass a NULL argument, it returns NULL.

Let's see an example:

MariaDB [(none)]> SELECT LPAD ('TheMariaDBDatabase',20,'A');

Here we can see the string passed is of 18 characters and 'A's are added to make is 20 character long.

MariaDB [(none)]> SELECT LPAD ('TheMariaDBDatabase',26,'Abc');

MariaDB [(none)]> SELECT LPAD ('TheMariaDBDatabase',26);

Here we have not mentioned padstr so it adds spaces prior to the string.

16. LTRIM() Function:

Syntax:

LTRIM (str);

This function trims the space characters from the left-hand side of the string.

If we pass a NULL argument, it returns NULL.

Lets see examples:

MariaDB [(none)]> SELECT LTRIM (' TheMariaDBDatabase');

Here we can see extra space at the left is eliminates.

MariaDB [(none)]> SELECT LTRIM (' The MariaDB Database ');

We can see the extra space only at the left is eliminated.

17. MID() Function:

Syntax:

MID(str, pos, len)

This function returns the substring starting from the pos to the len number of characters.

If we pass pos as a positive number, this function starts from the beginning of the string.

If we pass pos as a negative number, then this function starts from the end of the string, and it counts backward.

Let's see an example:

MariaDB [(none)]> SELECT MID('TheMariaDBDatabase',1,5);

Here first 5 characters are returned.

MariaDB [(none)]> SELECT MID('TheMariaDBDatabase',4,5);

Here the result starts from 4th character and 5 characters from that position are retrived.

MariaDB [(none)]> SELECT MID('TheMariaDBDatabase',-4,5);

Here we have mentioned negative pos variable which starts the substring backward.

18. POSITION() Function:

Syntax:

POSITION( substring IN string)

This function is equivalent to LOCATE().

Let's see an example:

MariaDB [(none)]> SELECT POSITION('B' IN 'Maria Database');

MariaDB [(none)]> SELECT POSITION('b' IN 'Maria Database');

Here we can see search is case insensitive so it returns same number as for previous query

MariaDB [(none)]> SELECT POSITION('z' IN 'Maria Database');

No match found so 0.

19. REPEAT() Function

Syntax:

SELECT REPEAT(str, num);

This function repeats the string provided for the num number of times.

If we pass a number less than one function will return an empty string.

Lets see example:

MariaDB [(none)]> SELECT REPEAT('Z',4);

Z is repeated 4 times.

MariaDB [(none)]> SELECT REPEAT('ZMZ',4);

The whole string is repeated 4 times.

MariaDB [(none)]> SELECT REPEAT(123,4);

The numbers are also repeated.

MariaDB [(none)]> SELECT REPEAT('ASA',0);

0 is passed so empty string is returned.

20. REPLACE() Function:

Syntax:

REPLACE( str, from_substr, to_substr);

This function replaces all the occurrences of the mentioned string. It returns str replacing all the occurrences of from_substr by to_str.

This function performs a case-sensitive search for from_str.

Let's see an example:

MariaDB [student]> SELECT REPLACE ('It is a rainy day’, 'rainy','sunny');

We can see the word 'rainy' is changed to 'sunny'.

MariaDB [student]> SELECT REPLACE ('It is a rainy day, 'RAINY','sunny');

Here we can see the string is not changed as the function performs a case-sensitive search.

MariaDB [student]> SELECT REPLACE ('It is a rainy day', 'x','z');

If no matched found original string is returned.

MariaDB [student]> SELECT REPLACE (12334536,3 ,8);

We can also pass numeric data.

21. REVERSE() Function

Syntax:

REVERSE(str_s);

This function returns the str_s with the order of the characters reversed.

We can also use this function with multi-bytes.

Let's see an example:

MariaDB [student]> SELECT REVERSE ('The MariaDB');

MariaDB [student]> SELECT REVERSE ('1233');

We can also pass numeric data in quotes.

MariaDB [student]> SELECT REVERSE (1233);

22. Right() Function

Syntax:

RIGHT(str, len);

This function returns the rightmost len characters of the string str. If any argument passed is NULL, the result is NULL. If the len exceeds the length of the string, the function returns the original string str.

Let's see an example:

MariaDB [student]> SELECT RIGHT ('The MariaDB Database',2);

We can see last 2 characters are returned

MariaDB [student]> SELECT RIGHT ('The MariaDB Database',44);

Here len exceeds so original string is returned.

23. RPAD() Function.

Syntax:

RPAD( str, len [,pad_str])

This function returns the string str, right-padded with the string mentioned pad_str to the length -len provided. If the string str is longer than len, the returning value is lessened to len characters. If we skip the pad_str as a parameter, the function pads the spaces.

Earlier, MariaDB 10.3.1, the argument pad_str, was compulsory to mention.

If we pass a NULL parameter, the result is NULL.

Let's see examples:

MariaDB [student]> SELECT RPAD('The MariaDB Database', 25,'Z');

Here Z is appended at the end of the string till the length of the string becomes 25.

MariaDB [student]> SELECT RPAD('The MariaDB Database', 5, 'Z');

Here the len mentioned is less than the original string, so the len number of characters is returned.

24. RTRIM() Function

Syntax:

RTRIM(string_s);

This function eliminates all the space characters from the right side of the spring string_s. If we pass NULL as an argument, the result is NULL.

Let's see an example:

MariaDB [student]> SELECT RTRIM('The MariaDB Database ');

MariaDB [student]> SELECT RTRIM('The     MariADB    Database ');

Here we can see extra spaces at the end are eliminated and not the ones between string.

25. SPACE() Function:

Syntax:

SPACE(X);

This function returns the string consisting of x space characters. If NULL is passed, NULL is returned as a result.

Let's see an example:

MariaDB [student]> SELECT QUOTE(SPACE(6));

In this example, we have used the QUOTE() function, which adds quotes to the strings passed. We can see a string with 6 spaces is returned.

26. STRCMP() Function:

Syntax:

STRCMP( str_1, str_2);

This function compares two strings passed. If the strings are of the same length, the function returns 0.

If the first argument is shorter than the other one according to the current sort order, it returns -1

and If the first argument is larger than the other one according to the current sort order, it returns 1.

Lets see example:

MariaDB [student]> SELECT STRCMP('The MariaDB Database','The MySQL Database');

Here we can see First string is larger, so it returns -1.

MariaDB [(none)]> SELECT STRCMP('The MySQL Database', 'The MariaDB Database');

Next, the second/ string is larger so the result is 1.

MariaDB [(none)]> SELECT STRCMP('The MySQL Database','The MySQL Database');

Finally both the strings are equal so result is 0.

27. SUBSTRING() Function

Syntax:

SUBSTRING(str , start_pos),
SUBSTRING(str FROM start_pos),
SUBSTRING(str, start_pos, len),
SUBSTRING(str FROM start_pos FOR len)

This function returns a substring of string str starting from start_pos of the len number of characters.

If the len is not provided, it returns a substring starting from start_pos.

The syntax with the FROM keyword is standard SQL syntax.

We can also pass negative values as the start_pos. In this case, the beginning of the substring is start_pos characters from the end of the string.

And if we pass a positive number as start_pos, the substring starts from the beginning of the string.

The SUBSTR and MID functions are equivalent to this function.

Let's see an example:

MariaDB [(none)]> SELECT SUBSTRING('Database',4);

Here we can string from 4th position is returned.

Next using FROM keyword:

MariaDB [(none)]> SELECT SUBSTRING('Database' FROM 4);

Same result is returned using FROM keyword.

MariaDB [(none)]> SELECT SUBSTRING('Database', 3, 5);

Here we have mentioned start_pos i.e string starts from 3th position and length mentioned is 5 so following 5 characters are returned.

MariaDB [(none)]> SELECT SUBSTRING('Database', -3);

Here we have mentioned start_pos as negative number so string characters are counted backwards so 3 characters are returned.

MariaDB [(none)]> SELECT SUBSTRING('Database'-4, 3);

Here last 4 characters of the string would be the result, but the len provided is 3, so three characters are returned.

MariaDB [(none)]> SELECT SUBSTRING('Database' FROM -4 FOR 3);

Using FROM and FOR keyword the result is same.

28. SUBSTR () Function

Syntax:

SUBSTR (str , start_pos, [len]),
SUBSTR(str FROM start_pos [For len[)

All the working is same as SUBSTRING Function

MariaDB [(none)]> SELECT SUBSTR('Database',4);

Here we can string from 4th position is returned.

MariaDB [(none)]> SELECT SUBSTR('Database', 3, 5);

Here we have mentioned start_pos i.e string starts from 3th position and length mentioned is 5 so following 5 characters are returned.

29. TRIM() Function

Syntax:

TRIM ([{BOTH | LEADING | TRAILING} [remstr] FROM str)
OR
TRIM( [resmstr FROM] str)

This function eliminates the remote prefixes or suffixes from the string str.

If we do not specify BOTH, LEADING, or TRAILING by default, BOTH is assumed.

rem_str is optional to specify; if we don't specify, spaces are eliminated.

if we pass NULL function returns NULL>

Examples:

MariaDB [student]> SELECT TRIM(LEADING 'Z' FROM 'ZZZZDATABASEZZZ');

We can see leading Zs are eliminated.

MariaDB [student]> SELECT TRIM(BOTH 'Z' FROM 'ZZZZDATABASEZZZ');

MariaDB [student]> SELECT TRIM(TRAILING 'Z' FROM 'ZZZZDATABASEZZZ');

We can see last Zs are eliminated.

30. UPPER() Function

Syntax:

UPPER(str)

This function returns str with all the characters converted to uppercase according to the current character set mapping. The default character set mapping is latin1.

This function cannot be used for BINARY, VARBINARY, and BLOB.

Example:

MariaDB [(none)]> SELECT UPPER('the database');

MariaDB [(none)]> SELECT UPPER('The DataBase');

31. UCASE() Function

Syntax:

UCASE(str)

All the working is same as UPPER()

Example:

MariaDB [(none)]> SELECT UCASE('the database');

MariaDB [(none)]> SELECT UCASE('The DataBase');

MariaDB Tutorial

MariaDB Tutorial

MariaDB - String Functions

1. CONCAT() Function

2. LENGTH() Function:

3. LENGTHB() Function:

4. ASCII() Function:

5. CONCAT_WS () Function

6. FIELD() Function :

7. FORMAT() Function

8. ELT() Function

9. INSERT() Function:

10. INSTR Function

11. LCASE() Function:

12. LEFT() Function

13. LOCATE() Function:

14. LOWER() Function

15. LPAD() Function

16. LTRIM() Function:

17. MID() Function:

18. POSITION() Function:

19. REPEAT() Function

20. REPLACE() Function:

21. REVERSE() Function

22. Right() Function

23. RPAD() Function.

24. RTRIM() Function

25. SPACE() Function:

26. STRCMP() Function:

27. SUBSTRING() Function

28. SUBSTR () Function

29. TRIM() Function

30. UPPER() Function

31. UCASE() Function