如何在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

TableA

**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.

我的输出表应该是

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屋！