How to write a SQL query in tabular formato

Question

I want to write a SQL query for the following:

1. |7|30|0|11|31
2. |07|30|00|11|13
3. |30|03
4. |30|68|71

I want the end result to be:

7
30
0
11
31

So far the code I have written eliminates | and concatenates all into one string.

SQL statement:

select replace(replace(replace(a_list,'|' ,' '),char(10),''),' ')

Result: 73001131

I have tried the answer given to my SQL query but it still doesn't work. Here is the screenshot for it:

Answer 1

You can use the built int string_split (SQL2016 onwards I think) to do this. If you have an older version, search for SQL Server String Split functions and there are lots of examples out there.

declare @s varchar(50) = '|7|30|0|11|31'

SELECT * from string_split(@s, '|') where len(value)>0

Returns the following rows