SQL Select that inserts a new row based on a delimiter
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