我们如何在 sql 中解析一个波浪号分隔的列来创建多个列? [英] How can we parse a tilde delimited column in sql to create several columns?
问题描述
我有一个表 TableA 有一个波浪号操作的列 ColumnA
I have a table TableA with a tilde operated column ColumnA
表A
**ColumnA**
123~abc~def~~~~~ghi~j~k~lmn~op~~~
231~a~dfg~wer~~~~~~~hijkl~~~
正如我们在上面的两行中看到的,它是用'~'分隔的.我基本上想将值分成单独的列.有 15 个 '~' 运算符.
As we can see in the above two rows, it is '~' separated. I basically want to separate the values into individual columns. There are 15 '~' operators.
我的输出表应该是这样的
My output table should be something like
Col1 Col2 Col3 Col4 . . .. . . .. .. .. .. .
123 abc def . .. . .. .. ... .. . . .
我在 DB2 中有一个可以执行此操作的查询,但它需要 15 个子查询才能完成此任务,因为有 15 个~"运算符.如下所示:
I have a query in DB2 which will do this but it requires 15 subqueries to achieve this task as there are 15 '~' operators. Given below:
SELECT substr(ColumnA, 1, LOCATE('~', ColumnA)-1) AS Col1,
substr(ColumnA, charindex('~', ColumnA)+1, LEN(ColumnA)) AS Other
FROM TableA
我仅通过上述查询分隔 Col1.如果我想分隔 15 列,我将不得不对此进行 15 次子查询.
I am separating Col1 only by the above query. If I wish to separate 15 columns, I will have to subquery this 15 times.
有没有更好的方法来做到这一点?
Is there a better way to do this?
谢谢
推荐答案
有人好心为 DB2 写了这个拆分函数
Someone was kind enough to write this split function for DB2
http://www.mcpressonline.com/sql/techtip-create-an-sql-function-to-split-a-delimited-list.html
CREATE FUNCTION QGPL.SPLIT (
@Data VARCHAR(32000),
@Delimiter VARCHAR(5))
RETURNS TABLE (
ID INT,
VALUE VARCHAR(256))
LANGUAGE SQL
DISALLOW PARALLEL
DETERMINISTIC
NOT FENCED
RETURN
WITH CTE_Items (ID,StartString,StopString) AS
(
SELECT
1 AS ID
,1 AS StartString
,LOCATE(@Delimiter, @Data) AS StopString
FROM SYSIBM.SYSDUMMY1
WHERE LENGTH(@Delimiter)>0
AND LENGTH(@Data)>0
UNION ALL
SELECT
ID + 1
,StopString + LENGTH(@Delimiter)
,LOCATE(@Delimiter, @Data, StopString + LENGTH(@Delimiter))
FROM
CTE_Items
WHERE
StopString > 0
)
SELECT ID, SUBSTRING(@Data,StartString,
CASE WHEN StopString=0
THEN LENGTH(@Data)
ELSE StopString-StartString END)
FROM CTE_Items;
这篇关于我们如何在 sql 中解析一个波浪号分隔的列来创建多个列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!