跨多列拆分字符串的元素 [英] Splitting elements of a string across multiple columns
本文介绍了跨多列拆分字符串的元素的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在 MySQL 中将包含带有文章引用的字符串的列拆分为多个列.例如下面的字符串
I would like to split a column containing a string with article references into multiple columns in MySQL. For example, the following string
North American Birds 53(1) 1999: 27-29
将被拆分为 'North American Birds', 53, 1, 1999, 27-19.我知道我可以使用 substring_index 来做一些这样的事情,但这不适用于从卷 (53) 中拆分期刊名称 (North American Birds).知道我该怎么做吗?
would be split into 'North American Birds', 53, 1, 1999, 27-19. I know I can do some of this with substring_index, but this will not work for splitting the journal name (North American Birds) from the volume (53). Any idea how I can do that?
推荐答案
您可以使用数字作为分隔符.
You can use the number as a separator.
缓慢而丑陋的代码,但我想它可以工作
SELECT
s.id
,SUBSTRING(s.title,1, PosOfFirstNumber-1) as booktitle
,SUBSTRING(s.title, PosOfFirstNumber) as Remainder
FROM
(SELECT
id
,title
,LEAST(
IFNULL(NULLIF(LOCATE('1',title),0),999)
,IFNULL(NULLIF(LOCATE('2',title),0),999)
,IFNULL(NULLIF(LOCATE('3',title),0),999)
,IFNULL(NULLIF(LOCATE('4',title),0),999)
,IFNULL(NULLIF(LOCATE('5',title),0),999)
,IFNULL(NULLIF(LOCATE('6',title),0),999)
,IFNULL(NULLIF(LOCATE('7',title),0),999)
,IFNULL(NULLIF(LOCATE('8',title),0),999)
,IFNULL(NULLIF(LOCATE('9',title),0),999)
,IFNULL(NULLIF(LOCATE('0',title),0),999)
)) as PosOfFirstNumber
FROM table1 ) s
这篇关于跨多列拆分字符串的元素的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文