MySQL get all characters before specific character

Question

I have a table where I extract some values, one column values can contain "value1|value2|value3", but I only want to get the characters before the | - "value1". This is what I tried, but it doesn't work.. What am I doing wrong? Thanks! $sql = "SELECT * LEFT('Like', LOCATE('|', 'Like')-1) FROM $tablename WHERE `Parent` = '0' AND Type LIKE 'top' ORDER BY `Order` ASC"; I want to use this for ALL values, not just one field..

Accepted Answer

you need the following statement to get that portion of [ColName]:

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

If you want to select multiple columns into the same recordset column you can union all with something like 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 on multiple columns, script the creation of the sql.

MySQL get all characters before specific character

Solution

Problem