Saturday, August 3, 2013

Split functions

1.
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
GO
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

2.
CREATE FUNCTION [dbo].[fn_Split_Up_Ids](   @Param_Ids varchar(500))RETURNS @Id_Table TABLE(IDField int)AS
BEGIN     IF (LEN(@Param_Ids) <= 0)       RETURN   DECLARE @CommaPos smallint   SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))           IF @CommaPos = 0       INSERT INTO @Id_Table               VALUES(CONVERT(BIGINT ,RTRIM(LTRIM(@Param_Ids))))   ELSE        BEGIN           WHILE LEN(@Param_Ids) > 1        BEGIN         SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))             INSERT INTO @Id_Table                       VALUES(CONVERT(INT ,SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos - 1)))         SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))           SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))          IF @CommaPos = 0        BEGIN                 INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))                 BREAK           END         END       END       RETURN   END
GO
select * from fn_Split_Up_Ids('1,2,3')