CODEWITHSUNDEEP
sqlsql-server-2005split
James
James

Reputation: 11

SQL Query to split a string

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

Upvotes: 1

Views: 4709

Answers (2)

Tahbaza
Tahbaza

Reputation: 9548

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/

Upvotes: 1

suryakiran
suryakiran

Reputation: 1996

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

Upvotes: 0

Related Questions