MySQL trim or get specific set of string using substring locate

I have a column named cea_no from my table. But I want only specific string from it. I already tried several methods but it it wont work for me. Do you have any idea how could I achieve this?

Here is my query

select id, cea_no, substring(cea_no, 1, locate('/', cea_no)) as x  from xp_guru_listings limit 10;

The result

+----+-----------------------------+------------------+
| id | cea_no                      | x                |
+----+-----------------------------+------------------+
|  1 | "CEA: R017722B / L3009740K" | "CEA: R017722B / |
|  2 | "CEA: R016023J / L3009793I" | "CEA: R016023J / |
|  3 | "CEA: R011571E / L3002382K" | "CEA: R011571E / |
|  4 | "CEA: R054044J / L3010738A" | "CEA: R054044J / |
|  5 | "CEA: R041180B / L3009250K" | "CEA: R041180B / |
|  6 | "CEA: R030152G / L3008022J" | "CEA: R030152G / |
|  7 | "CEA: R057318G / L3008022J" | "CEA: R057318G / |
|  8 | "CEA: R043256G / L3002382K" | "CEA: R043256G / |
|  9 | "CEA: R026068E / L3002382K" | "CEA: R026068E / |
| 10 | "CEA: R053784I / L3008022J" | "CEA: R053784I / |
+----+-----------------------------+------------------+

I only want the R017722B part. Removing the "CEA: , the second set of strings after the /part of the string. How can I do that? Thanks a lot. Im using MySQL xampp.

Answer

I would use SUBSTRING_INDEX here:

SELECT
    id,
    cea_no,
    select
    SUBSTRING_INDEX(SUBSTRING_INDEX(cea_no, 'CEA: ', -1), ' / ', 1) AS x
FROM xp_guru_listings;

To see how this trick works, consider a cea_no value of CEA: R017722B / L3009740K. The first call to SUBSTRING_INDEX returns R017722B / L3009740K, and the second call returns R017722B.

Advertisement

Answer