CODEWITHSUNDEEP
sql-serverstringsql-server-2008
Arunprasanth K V
Arunprasanth K V

Reputation: 21931

Split a substring that returns from a split function

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

Upvotes: 8

Views: 1197

Answers (6)

Salman Arshad
Salman Arshad

Reputation: 272236

You can use PATINDEX function to split the string by multiple delimiters. Here is the complete function and sample output. 

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

Upvotes: 1

I A Khan
I A Khan

Reputation: 8859

Use this may be help

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

Upvotes: 0

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

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','_','|'),'|')

Upvotes: 7

ughai
ughai

Reputation: 9890

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

Upvotes: 5

Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6672

Does this 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 ;

Upvotes: 1

Yu Yenkan
Yu Yenkan

Reputation: 765

in sql there is a CURSOR

then use fetch to get each SubString from 1st select

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

Upvotes: 2

Related Questions