选择逗号分隔字符串中的不同子字符串

编程入门 行业动态 更新时间:2024-10-27 08:27:15
本文介绍了选择逗号分隔字符串中的不同子字符串 |数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述

我有一个 csv 文件,我导入到我的数据库中,其中有一列用逗号分隔的值,我想要做的是从这些列中获取所有没有重复的值并将其放在只有一列中

I've a csv file that I imported to my database in which there are a column with comma separated values and what I wanna do is get all the values without duplicates from these column and put it in a only one column

这是桌子

这就是我想做的:

我不想修改原始列,只是在此形状中显示这些值,以了解逗号分隔的字符串可能采用的所有不同值

I don't want to modify the original column just show these values in this shape for know all diferentes values that the comma separated strings could take

我认为在这个问题 SQL 将值拆分为多行 中有答案.它提供了以下代码:

I think that in this question SQL split values to multiple rows there are the answer. It provides this code:

select tablename.id, SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name from numbers inner join tablename on CHAR_LENGTH(tablename.name) -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1 order by id, n

但我无法深入理解.为什么有两个substring_index?n.numbers 有什么用?名字"有什么用?关键词?等

But I can't deeply understand it. Why there are two substring_index? What's it's the use of n.numbers? What's the use of "name" keyword? etc.

如果有人可以澄清我,或者如果在其他问题中更好地解释,那么在这种情况下,这个问题可以链接到那个或借用

It would greatly appreciated if someone can clarify me that or if in other question it better explained then in this case this question will can linked to that or borrow

推荐答案

为什么有两个 substring_index?

Why there are two substring_index?

想象一下 tablename.name = 'A,B,C' 和 numbers.n = 2.

查看内部函数调用的结果

Look the result of inner function call which is

SUBSTRING_INDEX(tablename.name, ',', numbers.n) -- i.e. SUBSTRING_INDEX('A,B,C', ',', 2)

它获取子部分直到指定的第 2 个分隔符 - 即它给出 'A,B'.

It gets the subpart until specified, 2nd, delimiter - i.e. it gives 'A,B'.

现在看看外部函数如何处理这个输出.这是

Now look what the outer function do with this output. It is

SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) -- i.e. SUBSTRING_INDEX('A,B', ',', -1)

它从末尾获取子部分,直到指定的第一个分隔符 - 即它获取最后一个子字符串,即 'B'.

It gets the subpart until specified, 1st, delimiter, from the end - i.e. it gets last substring which is 'B'.

最后构造采用 numbers.n = 2 指定的第二个子串.

Finally the construction takes 2nd substring like specified by numbers.n = 2.

更多推荐

选择逗号分隔字符串中的不同子字符串

本文发布于:2023-10-26 22:09:42,感谢您对本站的认可!
本文链接:https://www.elefans.com/category/jswz/34/1531489.html
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文标签:字符串   逗号

发布评论

评论列表 (有 0 条评论)
草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍!