Split and get part of a string by separator in MySQL

Question

Imagine you have a string data column with some separator string.

The substrings can have different length!

For example:

abcde@xyz
abc@vwxyz

How to output this column as two, or if it is easier just one part (left or right) of the string?

Answer 1

You can use Substring_Index() function; it returns a substring in the left side of the given count of occurrences of the given delimiter. If the count value is negative, then it returns all to the right of the delimiter.

Demo Schema (MySQL v5.7)

create table your_table_name(field_name varchar(255));
insert into your_table_name values('abcde@xyz');
insert into your_table_name values('abc@vwxyz');

---

Query #1

SELECT 
  field_name, 
  Substring_Index(field_name, '@', 1) AS left_part, 
  Substring_Index(field_name, '@', -1) AS right_part
FROM your_table_name;

| field_name | left_part | right_part |
| ---------- | --------- | ---------- |
| abcde@xyz  | abcde     | xyz        |
| abc@vwxyz  | abc       | vwxyz      |

---

View on DB Fiddle

Answer 2

You can do it with the SUBSTRING_INDEX(str,delim,count)

mysql> SELECT SUBSTRING_INDEX('abcde@xyz', '@', 1);
        -> 'abcde'
mysql> SELECT SUBSTRING_INDEX('abcde@xyz', '@', -1);
        -> 'xyz'

Source: MySQL :: MySQL 5.7 Reference Manual :: 12.5 String Functions and Operators