Split a substring that returns from a split function

Question

I have a string like below

a_b|c_d

and I need to split it based on |.

So, the result will be:

a_b
c_d

Then, I want to split it again with _, then result will be:

a
b

c
d

Is it possible to do this in a single step? Like when input the corresponding string, it will return a value like following:

a
b
c
d

I have created a function for split:

select items from dbo.splitdetails('a_b|c_d','|')

It will result:

a_b
c_d

But I don't know how can I proceed the next split with these results?

Using temporary table, I hope I can do this, but I need to use this inside a function. So I think temporary table is not a good option. Cursor is also an option but when I use cursor, it will degrade the performance because I have thousands of records.

My input is:

a_b|c_d

And desired out put is:

a
b
c
d

Answer 1

You can better use Sql Replace function first and then use your split function as below

select REPLACE('a_b|c_d','|','_')

results: a_b_c_d

Now use your Split function like below

select items from dbo.splitdetails('a_b_c_d','_')

EDIT:

All at a time

select items from dbo.splitdetails(REPLACE('a_b|c_d','|','_'),'_')

EDIT1:

In that case Use the Replace option in Reverse

select items from dbo.splitdetails(REPLACE('a_b|c_d','_','|'),'|')

Answer 2

You can use CROSS APPLY like this.

SELECT d.item,e.item from dbo.splitdetails('a_b|c_d','|') d
CROSS APPLY dbo.splitdetails(d.item,'_') e