Tag Archives: mysql string

MySQL: Get the tail of a string after the last delimiter

MySQL has several String functions to search, replace, extract information from a string.

We had a list of string values in the format “[something1]_R[something2]”. We wanted to convert the values on this column to only store the [something2] part of the string after the last ‘_R’.
The restriction was that we couldn’t ensure that [something1] has not any “_R” sequence of characters.

Some examples:

  • For the string “12345_R27″ we want only to store “27”
  • For the string “12_Rat_R87″ we want only to store “87”

MySQL has SUBSTRING function but to solve our problem we would need to locate the last occurrence of “_R” to know where we should start the substring. Fortunately, MySQL has another function SUBSTRING_INDEX that “Returns the substring from string str before count occurrences of the delimiter delim

With this function we can say that want the substring starting on 1st, 2nd, 3rd, … nth occurrence of the delimiter.

But we can also invoke with count=-1 which will return the substring of the original str after the last occurrence of the delim (delimiter).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select substring_index('12345_R27', '_R', -1)
    -> ;
+----------------------------------------+
| substring_index('12345_R27', '_R', -1) |
+----------------------------------------+
| 27                                     |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index('12_Rat_R87', '_R', -1);
+-----------------------------------------+
| substring_index('12_Rat_R87', '_R', -1) |
+-----------------------------------------+
| 87                                      |
+-----------------------------------------+
1 row in set (0.00 sec)