Select string split from table and then order by it

for example my table contains something like:

id	name	price	cost
1	FRUIT:BANANA	12$	13$
2	BANANA	15$	14$
3	CUCAMBER	5$	2$
4	PLASTIC:UMBRELLA	20$	5$
5	PLASTIC:TABLE	10$	50$
6	TABLE	12$	20$

as you can see, some of my names contains ":" in the middle, when I make select query I want the result will contain:

id	name	price	cost
1	BANANA	12$	13$
2	BANANA	15$	14$
3	CUCAMBER	5$	2$
4	UMBRELLA	20$	5$
5	TABLE	10$	50$
6	TABLE	12$	20$

I found some function that called STRING_SPLIT, but it works only if I from is after "FROM", not found function that I can use in SELECT.

Thanks

CodePudding user response：

Use the function SUBSTRING_INDEX() to extract the part of the name after the : (if it exists):

SELECT id, 
       SUBSTRING_INDEX(name, ':', -1) name,
       price,
       cost
FROM tablename
ORDER BY name;

See the demo.

CodePudding user response：

MySQL SUBSTRING_INDEX() function can solve your problem. See the manual

SELECT
    id,
    SUBSTRING_INDEX(name, ':', -1) AS name,
    price,
    cost
FROM
    your_table