Split a substring returning from a split function

Question

I have a line as shown below

a_b|c_d 

and I need to break it based on | .

So, the result will be:

 a_b c_d 

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

 a b c d 

Is it possible to do this in one step? As with entering the appropriate string, it will return a value similar to the following:

 a b c d 

I created a function for split:

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

This will lead to:

 a_b c_d 

But I do not know how I can continue the next split with these results?

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

My input:

 a_b|c_d 

And the desired result:

 a b c d 

Answer 1

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

 select REPLACE('a_b|c_d','|','_') results: a_b_c_d 

Now use the Split function as shown 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 this 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 

Answer 3

in sql there is CURSOR

then use fetch to get each SubString from 1st pick

 DECLARE db_cursor CURSOR FOR select items from bdo.splitdetails('a_b|c_d','|') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @items WHILE @@FETCH_STATUS = 0 BEGIN select SubString from bdo.splitdetails(@items,'_') --do something on SubString-- FETCH NEXT FROM db_cursor INTO @items END CLOSE db_cursor DEALLOCATE db_cursor 

Answer 4

Does this help help ..

 With value(Item) as ( select items from bdo.splitdetails('a_b|c_d','|') ) Select x.Items from value a cross apply ( select items from bdo.splitdetails(a.Item,'_') ) x ; 

Answer 5

You can use the PATINDEX function to split a string into multiple delimiters. Here is the full output of the function and the selection.

 CREATE FUNCTION [dbo].[splitdetails] ( @input VARCHAR(100), @delim VARCHAR(100) ) RETURNS @table TABLE ( items VARCHAR(100) ) AS BEGIN DECLARE @index INT; SET @index = PATINDEX(@delim, @input) WHILE @index > 0 BEGIN INSERT INTO @table SELECT LEFT(@input, @index - 1) SET @input = RIGHT(@input, LEN(@input) - @index) SET @index = PATINDEX(@delim, @input) END INSERT INTO @table SELECT @input RETURN END GO SELECT items FROM dbo.splitdetails('a_b|c_d', '%[_|]%') -- a -- b -- c -- d SELECT items FROM dbo.splitdetails('xxx|yyy', '%[_|]%') -- xxx -- yyy SELECT items FROM dbo.splitdetails('xxxxxxx', '%[_|]%') -- xxxxxxx 

Answer 6

Using this can help.

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