MySQL在特定字符之前获取所有字符

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".

我有一个表格,我提取一些值,一列值可以包含“value1 | value2 | value3”,但我只想获取前面的字符。 - “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";

$ 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..

我想将它用于所有值,而不仅仅是一个字段..

3 个解决方案

#1

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

你需要以下语句来获取[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.

如果要在多个列上使用它,请编写sql的脚本。

#2

Two things: (1) you don't have a comma between your * and the expression you're trying to do with LEFT and (2) you're putting like in quotes, so the functions are working on the constant value like instead of your column named like. Try putting like in backticks.

两件事:(1)你的*和你试图用LEFT表达的表达式之间没有逗号,(2)你在引号中使用逗号,所以函数正在处理常量值,而不是您的列名为。尝试像反叛一样。

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

You can also use the MySQL SUBSTRING_INDEX function for this:

你也可以使用MySQL SUBSTRING_INDEX函数:

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

#3

I think there is an easy way:

我认为有一个简单的方法:

strSplit(ColName,'|',1)

Am I correct?

我对么?

#1