MariaDB Substring [11 Examples]

In this MariaDB tutorial, we will study the use of MariaDB Substring and we will also cover some examples. The lists of the topic that comes under discussion are given below:

MariaDB Substring Function
MariaDB Substring Index
MariaDB Substring Find
MariaDB Substring Regex
MariaDB Substring Position
Maraidb Substring Left
Mariadb Substring Count
Mariadb Substring Last Character
Mariadb Substring Delimiter

Table of Contents

MariaDB Substring Function

The Substring function in MariaDB allows you to extract a substring from a string. The syntax of the MariaDB Substring function is given below:

SUBSTRING (string,position);
SUBSTRING (string from position);
SUBSTRING (string,position,length);
SUBSTRING (string from position for length);

The syntax explanation:

String : It’s required as per user’s string name.
start_position: The extraction’s position. [Note, the first position of starting string is always 1].
length: [Optional] It returns number of characters to be extracted. It will return the whole string if length or parameters is omitted or provided as null.

The example of the MariaDB substring function is given below:

SELECT SUBSTRING('USA is best for IT sector',3);

As this query example explains that string is written as ‘USA is best for IT sector’ and the length is ‘3’ which means that it will start substring function from ‘A’ character in the string and bring the output as “A is best for IT sector”.

Read MariaDB LIMIT

MariaDB Substring Index

The syntax of the MariaDB substring index is given below:

substring_index(string,delimiter,position);

Before counting the circumstances of the delimiter delim, it returns a substring from string str. Everything to the left of the delimiter delim will be returned to the string str if the count value is positive.

If the count value is negative, the string str will be returned to everything to the right of the delimiter delim. When executing and searching for delim, it supports case sensitive matching.

The example with an output of MariaDB substring index is given below:

SELECT SUBSTRING_INDEX('www.sqlserverguides.com','.',2);

This example explains that str_name as “www.sqlserverguides.com”, delim as dot sign[.] and position_number as 2 means that delim is dot sign[.] and pos is 2 and str is ‘www.sqlserverguides.com’.

In MariaDB, the substring index function when the position is 2 which means that it will start from left to right as the position is a positive number. So the after second dot sign it will remove 2 words from its index substring. Therefore, the output will be “www. sqlserverguides”.

Read MariaDB varchar

MariaDB Substring Find

In the MariaDB substring find() function, you need to use locate() function. It returns locations of the first appearance of a substring in a string. The syntax of the location function in MariaDB is given below:

LOCATE(substring, string,[start_position]);

The syntax explanation is given below:

Substring: The substring is used to search in string.
String: The string searched.
Start_position: [optional] The position in string where search to be started. If the start_position is omitted, it defaults to 1 which is first position of the string.

NOTE:

The first position in string is 1.
The substring is not found in string, then locate() will provide 0 as output.

The example of MariaDB substring find is given below:

SELECT LOCATE('s','sqlserverguides',2);

MariaDB substring find example — MariaDB Substring Find

As this query example that by using the MariaDB substring locate function, the substring is ‘2’, the string is ‘sqlserverguides’ and start_position is ‘2’. When the substring is at ‘2’ and start_position is ‘2’ then it will start counting from position 2 which is character ‘q’ in string and it will look for ‘s’ character in it.

When it gets character ‘s’ in the string. it will stop at that moment and it will give output as a number for ‘s’ character in the string ‘sqlserverguides’ as 4 output.

Read MariaDB LIKE Operator

MariaDB Substring Regex

It is an extension of substring and it was also introduced in MariaDB 10.0.5. It allows you to extract a string from a substring using regular expression pattern matching. The syntax of MariaDB substring regular expression is given below:

regexp_substr(subject, pattern);

It returns part of a string subject that matches with regular expression pattern or an empty string if subject is not found. It collation case sensitive can also be overwritten by using (!?) and (?-!) PCRE flags. If you want to know more about PCRE documentation.

The syntax of MariaDB substring regex is given below:

SELECT REGEXP_SUBSTR('birth and death','de(e|a|i|o)th');

The MariaDB substring regular expression example explains that keyword subject is ‘birth and death and the pattern keyword are “de(e|a| I |o)” which means that de-a-the word spells like this.

But the user forgot to spell the death word, so the user uses regular expression pattern matchmaking in which he/she uses the ‘|’ sign as an option to spell the “death” word.

Read MariaDB query examples

MariaDB Substring Position

It is used to find the location of a substring in the string and it will also return the location of the first occurrence of the substring in the string. If the substring is not present in the string then it will return 0.

The syntax of MariaDB Substring Position is given below:

position (substring IN string);

This function accepts two parameters as given below:

Substring: the string to be searched for.
String: the string in which search operaton is done.

The example of MariaDB substring position is given below:

As above example explains that by using the MariaDB substring position function, IN keyword is used between substring ‘s’ and string ‘sqlserverguides’.

The function says, to find ‘s’ substring position in the string “sqlserverguides‘. When the substring looks for stopping by the MariaDB it will the position value as 1 in output.

Read How to create a database from an SQL file in MariaDB

MariaDB Substring Left

It allows you to extract a substring from a string, starting from the left-most character. the syntax of MariaDB substring left is given below:

LEFT (string, number_of_characters);

The example of MariaDB substring left is given below:

SELECT LEFT('sqlserverguides.com',1);

MariaDB substring left example — MariaDB **Substring Left**

As MariaDB Substring Left function example explains that by using the left function for a substring, it will start counting from left as the first left character of a string is always 1.

So in query, the string is ‘sqlserverguides’ and ‘number_of_characters’ is ‘1’, then by using left position it will start from left direction and it will stop on number_of_characters value for the string. Therefore, the substring left function value is 1.

Read MariaDB Insert Into

MariaDB Substring Count

In this tutorial, to use count the substring we need to use the length() function. The length function is used to convert the value of the string into numbers. If the value of the string is null then the function returns null.

The made-up syntax of MariaDB Substring Count is given below:

SELECT LENGTH(SUBSTRING(string,position);

The syntax example of MariaDB Substring Count is given below:

SELECT LENGTH(SUBSTRING('Reebok is Perfect Shoes',19));

MariaDB substring count example — MariaDB Substring Count Example

The above example of the Count function explains that to find the count/ length of substring then you need to use the LENGTH() function in it. Just use the length function outside the substring function with all same parameters and the rest are the same.

Read MariaDB create procedure

MariaDB Substring Last Character

In this topic, you need to use again substring function again. The syntax of substring the last character is given below:

SUBSTRING (STRING from position for length);

The example of substring the last character is given below:

SELECT SUBSTRING('James Bond'FROM -1 FOR 1);

MariaDB substring Last Character — MariaDB Substring Last Character

The above example tells if you use the position as -1 and length as 1 then the last character of the string will show as output ‘d’. but if you change the position of the string to positive then it works dynamically.

Read How to Change Column in MariaDB

MariaDB Substring Delimiter

The index function in MariaDB uses a substring delimiter as a delim keyword. It is simple to obtain the found index of the substring using the delimiter in the delim keyword and the count keyword.

If the count value is negative, it will begin counting from the right to the left to determine the index function’s value, but if the count value is positive, it will begin counting dynamically.

When searching for delim, it returns a case-sensitive match. Returns null if the argument is null.

The syntax of the MariaDB substring delimiter is given below:

SELECT SUBSTRING_INDEX(str,delim,count);

The example MariaDB substring delimiter is given below:

SELECT SUBSTRING_INDEX('www.sqlserverguides.com','.',-2);

MariaDB substring delimiter example_1 — MariaDB Substring Delimiter Example_1

In this above example, by using MariaDB substring index function with delim keyword ‘.’ and count as ‘-2’.

This means that it will count from right to left direction but when it comes to the second dot[.] as delim then, it removes all words after that dot [.] sign. The output will be “sqlserverguides.com”

SELECT SUBSTRING_INDEX('www.sqlserverguides.com','.',2);

MariaDB substring index delimiter example_2

In this above example, by using MariaDB substring index function with delim keyword ‘.’ and count as ‘2’.

This means that it will count from left to right direction but when it comes to the second dot[.] as delim then, it removes all words after that dot [.] sign. The output will be “www. sqlserverguides”.