主页 / dba / 问题 / 174371
Accepted
DamnGenius
Asked: 2017-05-24 11:50:31 +0800 CST

基于分隔符插入新行的 SQL Select

  • 772

我有一个包含以下示例值的表：

ID VALUE
1 |856|421|
2 |123|795|7125|
3 |96412|85251|2245|
4 |4845|88422|9155|15154|
5 |1165|98742|

我怎样才能写一个 select 语句，结果将是：

    ID Value    
    1 |856|
    1 |421|
    2 |123|
    2 |795|
    2 |7125|
    3 |96412|
    3 |85251|
    3 |2245|
    4 |4845|
    4 |88422|
    4 |9155|
    4 |15154|
    5 |1165|
    5 |98742|

唯一不变的是每组数字总是包含在 | 之间。人物。有任何想法吗？

我们需要保留管道“|”。

sql-server sql-server-2012

2 个回答

  1. Best Answer

    我从StackOverflow 上的这个答案中借用了这个拆分字符串函数。由于您的数据包含在使用中，因此|您必须仅选择数据 <> '' 中的那些值，最后使用 CONCAT 函数再次添加定界符。

    create function dbo.splitString(@input Varchar(max), @Splitter VarChar(99)) returns table as
Return
    SELECT Split.a.value('.', 'VARCHAR(max)') AS Data 
    FROM (SELECT CAST ('<M>' + REPLACE(@input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a); 
GO

    Select id, concat('|', data, '|') value 
from mytable x 
cross apply dbo.splitString(x.value,'|') Y
where data <> '';
GO

    编号 | 价值  
-: | :------
 1 | |856|  
 1 | |421|  
 2 | |123|  
 2 | |795|  
 2 | |7125|
 3 | |96412|
 3 | |85251|
 3 | |2245|
 4 | |4845|
 4 | |88422|
 4 | |9155|
 4 | |15154|
 5 | |1165|
 5 | |98742|

    dbfiddle在这里

    • 3

  2. 这也可以通过递归 SQL 来完成，尽管我不能说这是否是个好主意。数据准备：

    create table #my_table (
    ID INT,
    [VALUE] VARCHAR(200),
    PRIMARY KEY (ID)
);

INSERT INTO #my_table VALUES (1, '|856|421|');
INSERT INTO #my_table VALUES (2, '|123|795|7125|');
INSERT INTO #my_table VALUES (3, '|96412|85251|2245|');
INSERT INTO #my_table VALUES (4, '|4845|88422|9155|15154|');
INSERT INTO #my_table VALUES (5, '|1165|98742|');

    递归查询：

    WITH rec_CTE AS (
    SELECT
      ID
    , [VALUE]
    , SUBSTRING([VALUE], 2,  CHARINDEX('|', [VALUE], 2) - 2) substr
    , CHARINDEX('|', [VALUE], 2) + 1 last_pos
    , LEN([VALUE]) LEN_VALUE
    FROM #my_table

    UNION ALL

    SELECT 
      mt.ID
    , rc.[VALUE]
    , SUBSTRING(rc.[VALUE], rc.last_pos , CHARINDEX('|', rc.[VALUE], rc.last_pos) - rc.last_pos) substr
    , CHARINDEX('|', rc.[VALUE], rc.last_pos) + 1 last_pos
    , rc.LEN_VALUE
    FROM #my_table mt
    INNER JOIN rec_CTE rc ON rc.ID = mt.ID
    WHERE rc.last_pos < rc.LEN_VALUE
)
SELECT ID, '|' + substr + '|'
FROM rec_CTE
ORDER BY ID, last_pos;

    结果：

    ╔════╦═════════╗
║ ID ║  VALUE  ║
╠════╬═════════╣
║  1 ║ |856|   ║
║  1 ║ |421|   ║
║  2 ║ |123|   ║
║  2 ║ |795|   ║
║  2 ║ |7125|  ║
║  3 ║ |96412| ║
║  3 ║ |85251| ║
║  3 ║ |2245|  ║
║  4 ║ |4845|  ║
║  4 ║ |88422| ║
║  4 ║ |9155|  ║
║  4 ║ |15154| ║
║  5 ║ |1165|  ║
║  5 ║ |98742| ║
╚════╩═════════╝
    • 2

相关问题