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
>Solution :
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.