1

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

nmc
  • 8,724
  • 5
  • 36
  • 68
James
  • 11
  • 1
  • 2
  • Just to be clear: you are looking to split by a specific character (.) and length based splitting just will not do. yes? – Tremmors Jun 27 '11 at 03:12
  • Yes, Exactly. I need to split a string based on a special character like ".,?" etc and have them displayed seperately. – James Jun 27 '11 at 03:13
  • possible duplicate of [T-SQL: Opposite to string concatenation - how to split string into multiple records](http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor) – Dan D. Jun 27 '11 at 03:18

2 Answers2

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/

Tahbaza
  • 9,486
  • 2
  • 26
  • 39
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',',')
suryakiran
  • 1,976
  • 25
  • 41