Wednesday, 5 June 2013

Split Funtions in sql server

Hi ,
In this article we will create a table value split function in sql server ,and use it in sql query.

2)Create Function::

   Create function [dbo].[SplitString]
          (
             @str nvarchar(4000),
             @separator char(1)
         )
    returns table
   AS
    return (
              with tokens(p, a, b) AS (
            select
              1,
              1,
           charindex (@separator, @str)
           union all
           select
           p + 1,
           b + 1,
         charindex (@separator, @str, b + 1)
         from tokens
         where b > 0
    )
     select
             p-1 ID,
              substring (
                @str,
                  a,
                case when b > 0 then b-a ELSE 4000 end )
                AS s     from tokens
   )

3)Test Case::


     declare
@your_str varchar(max)
     declare @saparated_by char(1)
     set @your_str ='sandipG@sql@blogspot@.@In'
     set @separated_by ='@'
     select id,s saprated_string from SplitString(@your_str,@saparated_by)

1 comment:

  1. This post has a full information i really love all of this information .

    Best Regards,
    Liza Curinto
    forex server

    ReplyDelete

8451831077