Início / dba / Perguntas / 174371
Accepted
DamnGenius
Asked: 2017-05-24 11:50:31 +0800 CST

SQL Select que insere uma nova linha com base em um delimitador

  • 772

Eu tenho uma tabela com os seguintes valores de exemplo:

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

Como posso escrever uma instrução select onde o resultado será:

    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|

A única constante é que cada grupo de números está sempre incluído entre | personagens. Alguma ideia?

Precisamos preservar os tubos '|'.

sql-server sql-server-2012

2 respostas

  1. Best Answer

    Eu peguei emprestada essa função de string dividida desta resposta no StackOverflow. Como seus dados estão incluídos usando |você deve selecionar apenas aqueles valores onde data <> '' e finalmente adicionar o delimitador novamente usando a função 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

    identificação | valor  
-: | :------
 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 aqui

    • 3

  2. Isso também pode ser feito com SQL recursivo, embora eu não possa dizer se isso é uma boa ideia. Preparação de dados:

    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|');

    Consulta recursiva:

    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;

    Resultados:

    ╔════╦═════════╗
║ 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

relate perguntas