将分隔的字符串值拆分为行 [英] Split delimited string value into rows
本文介绍了将分隔的字符串值拆分为行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
一些外部数据供应商想给我一个数据字段 - 管道分隔字符串值,我发现很难处理。
没有应用程序编程的帮助语言,有没有办法将字符串值转换成行?
有一个困难,但是,字段具有未知的分隔元素的数量。
数据库引擎是MySQL。
例如:
输入:Tuple(1,a | b | c)
输出:
$ b Tuple(1,b)
Tuple(1,c)
这是一种通用的方法:
- 计算分隔符的出现次数
length(val) - length(replace(val,'|','')) c $ c>
- 循环多次,每次抓取一个新的分隔值,并将值插入第二个表。
Some external data vendor wants to give me a data field - pipe delimited string value, which I find quite difficult to deal with.
Without help from an application programming language, is there a way to transform the string value into rows?
There is a difficulty however, the field has unknown number of delimited elements.
DB engine in question is MySQL.
For example:
Input: Tuple(1, "a|b|c")
Output:
Tuple(1, "a")
Tuple(1, "b")
Tuple(1, "c")
解决方案
It may not be as difficult as I initially thought.
This is a general approach:
- Count number of occurrences of the delimiter
length(val) - length(replace(val, '|', ''))
- Loop a number of times, each time grab a new delimited value and insert the value to a second table.
这篇关于将分隔的字符串值拆分为行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文