Split a Value in SqlSever

If we want values in the from of individual text so we can use following way:
1. If we have a text value combination so here is the way to separate these 2 values :


Declare @CompositeValue as Varchar(500)
set @CompositeValue='Text_Value'

Select Substring(@CompositeValue,0,CharIndex('_,@CompositeValue,1')) as Text

Select Substring(@CompositeValue,CharIndex('_,@CompositeValue,1')+1,Len(@CompositeValue)) as Value

2. There is another way to get split-ed value in a form of table :

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 05/10/2012 20:30:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split]
(
    @String nvarchar(max),
    @Delimiter char(1)
)
returns @Results TABLE (ID int identity, Item nvarchar(4000))
as
   begin
   declare @index int
   declare @slice nvarchar(max)

   select @index = 1
   if @String is null return

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

          insert into @Results(Item) values(ltrim(rtrim(@slice)))

          select @String = right(@String,len(@String) - @index)
          if len(@String) = 0 break
       end  
  
    return
end

GO

Happy Living , Happy Coding 
Yashpal Sharma