Ways to Select Everything Before/After a Certain Character in MySQL8 – STRING_INDEX() Functions – String Functions

SUBSTRING_INDEX(), This function returns everything before or after a certain character (or characters) in a string.

This function let you to state the delimiter to use, and you can also state which one (in the event that there’s more than one in the string). Delimiter can be single characters or multiple characters.

Returns the substring from string before count occurrences of the delimiter delim.

If count is positive, everything to the left of the final delimiter (counting from the left) is returned.

If count is negative, everything to the right of the final delimiter (counting from the right) is returned. We can also use a negative(-ve) value for the count. In this case, the beginning of the substring is position characters from the end of the string, rather than the beginning.

SUBSTRING_INDEX() performs a case-sensitive match when searching for delim. See illustrations 3.

A value of 0 for delim returns an empty string. See illustrations 5.

Note : This function is multibyte safe.

---

SUBSTRING_INDEX() : Syntax

SUBSTRING_INDEX(string , delimiter, count );

---

SUBSTRING_INDEX() : Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|left|left|left”]
Name, Required /Optional, Value Type, Description
string , Required, String , The input string.
delimiter, Required, String,  It will tells delimiter string in the input string..
count, Optional, Numeric , It tells till how many times delimiter should be found, till that match substring will be returned (in the event there are multiple occurrences of the delimiter in the string). It also states whether you want the substring either from the left or right
[/table]

---

SUBSTRING_INDEX() : Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns,
String, returns a substring starting from the left or right till the delimiter count is reached.
[/table]

---

SUBSTRING_INDEX() : Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]

---

SUBSTRING() Example 1 : Basic usage

Below is an example :

mysql> SELECT SUBSTRING_INDEX('www.tutorialmines.net', '.' ,2) as Output;
+-------------------+
| Output            |
+-------------------+
| www.tutorialmines |
+-------------------+
1 row in set (0.00 sec)

With a negative value for the position

mysql> SELECT SUBSTRING_INDEX('www.tutorialmines.net', '.' ,-2) as Output;
+-------------------+
| Output |
+-------------------+
| tutorialmines.net |
+-------------------+
1 row in set (0.00 sec)

---

SUBSTRING() Example 2 : If delimiter is string for e.g ‘and’

Below is an example :

mysql> SELECT SUBSTRING_INDEX('mysql and database and php','and', 2) as Output;
+---------------------+
| Output |
+---------------------+
| mysql and database |
+---------------------+
1 row in set (0.00 sec)

With a negative value for the position

mysql> SELECT SUBSTRING_INDEX('mysql and database and php','and', -2) as Output;
+-------------------+
| Output |
+-------------------+
| database and php |
+-------------------+
1 row in set (0.00 sec)

---

SUBSTRING() Example 3 : Case Sensitivity with word e.g ‘AND’

Incorrect case with word ‘AND’. As, it has case insensitive feature. So, it will be return the same string as input.

Below is an example :

mysql> SELECT SUBSTRING_INDEX('mysql and database and php','AND', 2) as Output;
+----------------------------+
| Output |
+----------------------------+
| mysql and database and php |
+----------------------------+
1 row in set (0.00 sec)

Correct case with word ‘and’. It will be return the string starting from left till delimiter count 2 is reached.

mysql> SELECT SUBSTRING_INDEX('mysql and database and php','and', 2) as Output;
+---------------------+
| Output |
+---------------------+
| mysql and database |
+---------------------+
1 row in set (0.00 sec)

---

SUBSTRING() Example 4 : NULL arguments

It will return NULL if any of the arguments is NULL. Let’s see the below example.

mysql> SELECT SUBSTRING_INDEX('www.tutorialmines.com','.', NULL) as Output1,
              SUBSTRING_INDEX('www.tutorialmines.com',NULL, 2) as Output2;
+---------+---------+
| Output1 | Output2 |
+---------+---------+
| NULL    |    NULL |
+---------+---------+
1 row in set (0.00 sec)

---

SUBSTRING() Example 5 : Using Zero(0) value for delimiter variable

Let’s see the below example.

mysql> SELECT SUBSTRING_INDEX('www.tutorialmines.com','.', 0) as Output1;
+---------+
| Output1 |
+---------+
|         |
+---------+
1 row in set (0.00 sec)

---

See all MySQL String functions MySQL 8 String Functions.

---

Related articles : LOCATE(), HEX(), CONCAT(), CONCAT_WS() , LOWER(), LTRIM(), INSTR(), POSITION().

---

PHP Related articles : SUBSTR(), SUBSTR_COUNT(), SUBSTR_COMPARE(), PHP STRING FUNCTIONS(), SUBSTR_REPLACE().