在MySQL中以逗号分隔的字符串中替换值? [英] Replace value within a comma-delimited string in MySQL?

问题描述

假设我在MySQL中具有以下逗号分隔的列值:foo,bar,baz,bar,foo2

Suppose I have the following comma-delimited column value in MySQL: foo,bar,baz,bar,foo2

用barAAA替换此字符串的第4位(在这种情况下为bar)的最佳方法是什么(以便将foo,bar,baz,bar,foo2更改为foo,bar,baz,barAAA,foo2)?请注意，bar出现在位置2以及位置4.

What is the best way to replace whatever is in the 4th position (in this case bar) of this string with barAAA (so that we change foo,bar,baz,bar,foo2 to foo,bar,baz,barAAA,foo2)? Note that bar occurs both in position 2 as well as position 4.

我知道我可以在MySQL中使用SUBSTRING_INDEX()来获取第4位的值，但是还无法弄清楚如何用新值替换第4位的值.

I know that I can use SUBSTRING_INDEX() in MySQL to get the value of whatever is in position 4, but have not been able to figure out how to replace the value in position 4 with a new value.

我需要通过不使用UDF或存储函数来执行此操作，方法是仅使用MySQL中的标准字符串函数(

I need to do this without creating a UDF or stored function, via using only the standard string functions in MySQL (http://dev.mysql.com/doc/refman/5.5/en/string-functions.html).

推荐答案

嗯...也许是这个?

SELECT @before := CONCAT(SUBSTRING_INDEX(`columnname`,',',3),','),
       @len := LENGTH(SUBSTRING_INDEX(`columnname`,',',4)+1
FROM `tablename` WHERE ...;

SELECT CONCAT(@before,'newstring',SUBSTRING(`columnname`,@len+1)) AS `result`
FROM `tablename` WHERE ...;

根据需要替换事物，但这应该就可以了.

Replace things as needed, but that should just about do it.

编辑:合并为一个查询:

SELECT
    CONCAT(
        SUBSTRING_INDEX(`columnname`,',',3),
        ',newstring,',
        SUBSTRING(`columnname`, LENGTH(SUBSTRING_INDEX(`columnname`,',',4)+1))
    ) as `result`
FROM `tablename` WHERE ...;

我不确定+1可能需要为+2，但这应该可行.

That +1 may need to be +2, I'm not sure, but that should work.

这篇关于在MySQL中以逗号分隔的字符串中替换值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！