MySQL retrieves all characters up to a specific character

Question

I have a table where I retrieve some values, the values ​​of one column may contain "value1 | value2 | value3", but I only want to get the characters before | - "value1".

This is what I tried, but it does not work. What am I doing wrong? Thanks!

$ sql = "SELECT * LEFT ('Like', LOCATE ('|', 'Like') - 1) FROM $ tablename WHERE Parent = '0' And enter LIKE 'top' ORDER BY Order ASC";

I want to use this for ALL values, and not just for one field.

Answer 1

to get this [ColName] part, you need the following statement:

 LEFT([ColName],INSTR([ColName],"|")-1) 

If you want to select multiple columns in one column of a recordset, you can combine everything with the following:

  SELECT LEFT(ColName,INSTR(ColName,"|")-1) AS FirstValue From $TableName; UNION ALL SELECT LEFT(ColName2,INSTR(ColName2,"|")-1) AS FirstValue From $TableName; 

If you want to use this in multiple columns, the script will create sql.

Answer 2

Two things: (1) you do not have a comma between your * and the expression you are trying to do with LEFT , and (2) you put quotes around like , so functions work with a constant like value instead of a column named like . Try putting like in backlinks.

 SELECT *, LEFT(`Like`, LOCATE('|', `Like`)-1) ... 

You can also use the MySQL function SUBSTRING_INDEX :

 SELECT *, SUBSTRING_INDEX(`Like`, '|', 1) ... 

Answer 3

I think there is an easy way:

 strSplit(ColName,'|',1) 

Am I right?