CODEWITHSUNDEEP
mysqlsqldatabasesubstring
Open Food Broker
Open Food Broker

Reputation: 573

Split and get part of a string by separator in MySQL

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?

Upvotes: 2

Views: 3687

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

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

Upvotes: 5

MigMolRod
MigMolRod

Reputation: 759

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

Upvotes: 4

Related Questions