SQL Select that inserts a new row based on a delimiter I have a table with the following sample values:

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


How can I do write a select statement where the result will be:

    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|


The only constant is that each group of numbers are always included between | characters. Any ideas?

We need to preserve the pipes '|'.
I borrowed this split string function from this answer on StackOverflow. Due your data is enclosed using | you must select only those values where data &lt;> '' and finally add the delimiter again using CONCAT function.




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 ('&lt;M&gt;' + REPLACE(@input, @Splitter, '&lt;/M&gt;&lt;M&gt;') + '&lt;/M&gt;' 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 &lt;&gt; '';
GO



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|



dbfiddle here