splitting a sql string

  • aaaaa,bbbb,ccccc,dddd,ffff,gggg,llll,kkkk,nnnnn

    How can split the above string to the following individual strings

    aaaaa

    bbbb

    ccccc

    dddd

    ffff

    gggg

    llll

    kkkk

    nnnnn

    Thanks

  • Create the following function which accepts a comma delimited, optionally quoted string and returns the separated result as a table:

     

    CREATE function fnSplitString (@s nvarchar(4000)) returns @strings table (value nvarchar(100)) as

     begin

      declare @temp nvarchar(100), @quote bit

      set @temp=''

      set @quote=0

      while len(@s)<>0

       begin

        if left(@s,2)=''''''

         begin

          if @quote=0

           insert into @strings values ('')

          else

           set @temp=@temp+'''' 

          set @s-2=substring(@s,3,len(@s)-2)

         end

        if left(@s,1)=''''

         begin

          if @quote=0 

           set @quote=1

          else

           begin

            set @quote=0

            insert into @strings values (@temp)

            set @temp=''

           end

          set @s-2=substring(@s,2,len(@s)-1)

         end

        if left(@s,1)=','

         begin

          if @quote=0

           begin

            if len(@temp)<>0

             insert into @strings values (@temp)

            set @temp=''

           end

          else

           set @temp=@temp+','

          set @s-2=substring(@s,2,len(@s)-1)

         end

        else if len(@s)<>0

         begin

          set @temp=@temp+left(@s,1)

          set @s-2=substring(@s,2,len(@s)-1)

         end

       end

      if len(@temp)<>0

       insert into @strings values (@temp)

      return

     end

    The following statement...

    select value from dbo.fnSplitString('aaa,bbb,''cc,c'',ddd')

    ...will return:

    aaa
    bbb
    cc,c
    ddd

    I'm going to be sooo embarassed now if someone comes up with a built-in method of doing this!

  • http://www.sommarskog.se/arrays-in-sql.html

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • DECLARE @name varchar(500)

    DECLARE @len int,@start int,@start1 int,@len_original int

    SET @start1=0

    SET @name='aaa,bbb,ccc,ddd,eee,fff'

    SELECT @len_original=LEN(@name)

    SELECT @len=LEN(@name)

    SET @start=1

    WHILE @len>1

    BEGIN

    SELECT SUBSTRING(@name, 1, CHARINDEX(',', @name)-1 ) as user_name

    SELECT @start=CHARINDEX(',', @name)+1

    SELECT @start1=@start1+@start

    SELECT @name=SUBSTRING(@name,@start,(@len-@start))

    SELECT @len=@len-@start+1

    IF @start1=@len_original

    BEGIN

    SELECT @name AS user_name

    BREAK

    END

    ELSE CONTINUE

    END

     

    how can i optimize the above code?

  • ALTER FUNCTION dbo.fnStringToTable(@CommaDelimList varchar(5000), @Delim char(1))

    --*********************************

    --Example;

    --select *

    --from  dbo.fnStringToTable('one, two, three, four', ',')

    --

    --Author: Ed Hellyer

    --Date:  Tuesday July 26th 2006

    --

    --*********************************

    RETURNS @List TABLE (Value varchar(100))

    AS

      BEGIN

     declare @Start int

     set @Start = 1

     while @Start is not null

       begin

          insert into @List(Value) select LTrim(RTrim(SubString(@CommaDelimList, @Start, abs(@Start - IsNull(NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0), Len(@CommaDelimList) + 1)))))

      set @Start = NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0) + 1

       end

     return

      END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply