SQL正则表达式基于定界符"/n"将一列(字符串)拆分为多行 [英] SQL Regular expression to split a column (string) to multiple rows based on delimiter '/n'

问题描述

我必须将一列拆分为多行.该列存储字符串，我们必须根据'/n'

I have to split a column to multiple rows. The column stores string, and we have to split delimiter based on '/n'

已编写以下查询.但无法指定^[/n].字符串中的另一个'n'也将被删除.请帮助解析字符串

Have written the below query. But not able to specify ^[/n]. The other 'n' in the string is also getting removed. Please help to parse the string

WITH sample AS 
( SELECT 101 AS id, 
        'Name' test, 
        '3243243242342342/n12131212312/n123131232/n' as attribute_1, 
        'test value/nneenu not/nhoney' as attribute_2
   FROM DUAL 
) 
-- end of sample data
SELECT id,
       test,
       regexp_substr(attribute_1,'[^/n]+', 1, column_value),
       regexp_substr(attribute_2,'[^/]+', 1, column_value)
  FROM sample,
       TABLE(
         CAST(
           MULTISET(SELECT LEVEL 
                       FROM dual  
                    CONNECT BY LEVEL <= LENGTH(attribute_1) - LENGTH(replace(attribute_1, '/n')) + 1
                   ) AS sys.OdciNumberList
         )
       )
 WHERE regexp_substr(attribute_1,'[^/n]+', 1, column_value) IS NOT NULL
/

推荐答案

首选方法是修复数据模型，因为这种方式存储的数据不是最佳的.无论如何，请尝试使用带有更多测试数据的该版本.我调整了正则表达式:

First choice would be to fix the data model as data stored this way is not optimal. At any rate, try this version with some more test data. I tweaked the regex's:

WITH sample AS 
( SELECT 101 AS id, 
        'Name' test, 
        '3243243242342342/n12131212312/n123131232/n' as attribute_1, 
        'test value/nneenu not/nhoney' as attribute_2
   FROM DUAL 
) 
-- end of sample data
SELECT id,
       test,
       regexp_substr(attribute_1,'(.*?)(/n|$)', 1, column_value, NULL, 1),
       regexp_substr(attribute_2,'(.*?)(/n|$)', 1, column_value, NULL, 1)
  FROM sample,
       TABLE(
         CAST(
           MULTISET(SELECT LEVEL 
                       FROM dual  
                    --CONNECT BY LEVEL <= LENGTH(attribute_1) - LENGTH(replace(attribute_1, '/n')) + 1
                      -- Counts substrings ending with the delimiter.
                      CONNECT BY LEVEL <= REGEXP_COUNT(attribute_1, '.*?/n')                    
                   ) AS sys.OdciNumberList
         )
       )
 WHERE regexp_substr(attribute_1,'(.*?)(/n|$)', 1, column_value, NULL, 1) IS NOT NULL
/

这篇关于SQL正则表达式基于定界符"/n"将一列(字符串)拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！