FIND_IN_SET 具有值的修剪功能 [英] FIND_IN_SET woth trim function for values

问题描述

我有一列包含逗号分隔值的字符串.我使用 FIND_IN_SET 来查询这个列，它工作正常，直到值和 , 之间有一个空格.我无法控制输入.我发现唯一可行的解​​决方案是在 FIND_IN_SET 函数中的列上运行 REPLACE.不幸的是，这将删除所有空格并可能返回不想要的结果.

I have a column that contains a string of comma delimited values. I use FIND_IN_SET to query this column and it works fine until there is a space between the value and the ,. I cannot control the input. The only solution I have found that works is by running REPLACE on the column within the FIND_IN_SET function. Unfortunately this will remove all spaces and could return undesired results.

blow 示例将返回表中的两行，而不是仅返回第一行.

The blow example would return both row in the table as opposed to the first one only.

col1                  | col2      
carpet , foo, bar     | myVal1
abc, 123 , car pet    | myVal2

查询

SELECT FIND_IN_SET('carpet', REPLACE(col1, ' ', ''));

有没有办法限制这个只修剪，

Is there a way of limiting this to only trim the space wither side of the ,

推荐答案

您可以尝试用逗号替换 ,[ ] 或 [ ], :

You could try replacing ,[ ] or [ ], with just comma:

SELECT
    col1,
    col2,
    FIND_IN_SET('carpet', REPLACE(REPLACE(col1, ', ', ','), ' ,', ',')) AS output
FROM yourTable;

演示

注意:此答案假设逗号周围最多有一个前导/尾随空格，并且您的实际数据本身不包含逗号.如果可以有任意数量的空格，则此答案将失败.在这种情况下，您真正​​需要的是正则表达式替换.MySQL 8+ 确实支持这一点，但更好的办法是规范化您的数据并停止像这样存储 CSV 数据.

Note: This answers assumes that there would be at most one leading/trailing space around the commas, and that your actual data itself does not contain commas. If there could arbitrary amount of whitespace, this answer would fail. In that case, what you would really need is regex replacement. MySQL 8+ does support this, but a better bet would be to normalize your data and stop storing CSV data like this.

这篇关于FIND_IN_SET 具有值的修剪功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！