SQL Query to split a string

Question

I have string "ABCD.EFGH.IJKL.MNOP". I am looking for a sql query from which I can get

first part ("ABCD"), Second Part ("EFGH"), Third Part ("IJKL"), Fourth Part ("LMNOP") from a large data. SO performance have to be kept in mind.

Thank you

Regards, James

Just to be clear: you are looking to split by a specific character (.) and length based splitting just will not do. yes? — Tremmors
– Tremmors, Commented Jun 27, 2011 at 3:12
Yes, Exactly. I need to split a string based on a special character like ".,?" etc and have them displayed seperately. — James
– James, Commented Jun 27, 2011 at 3:13
possible duplicate of T-SQL: Opposite to string concatenation - how to split string into multiple records — Dan D.
– Dan D., Commented Jun 27, 2011 at 3:18

Tahbaza · Accepted Answer · 2011-06-27 03:22:23Z

1

Jeff Moden wrote a series of articles on this recently, complete with performance comparisons of the most popular attempts at filling this common need. See this for the latest installment and the latest rev of his DelimitedSplit8K function http://www.sqlservercentral.com/articles/Tally+Table/72993/

answered Jun 27, 2011 at 3:22

Tahbaza

9,5542 gold badges28 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

suryakiran · Accepted Answer · 2011-06-27 03:12:51Z

0

I assuming that, you are looking for MSSQL. You can do that using a user defined function which returns table variable. Try this

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end

You can use this function is your SQL Queries

select * from dbo.Split('Test,Tested,To be Tested',',')

answered Jun 27, 2011 at 3:12

suryakiran

1,99426 silver badges41 bronze badges

2 Comments

James Over a year ago

Thank you very much !! Is there any way to have without a UDF as I have to write this in RDL file and seperate function creation may not be possible.

suryakiran Over a year ago

@James: Can you use a Stored Procedure?

Collectives™ on Stack Overflow

SQL Query to split a string

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related