Tuesday, July 14, 2009

Split value function SQL SERVER

Create FUNCTION [dbo].[SplitValues](@Str varchar(max),@Deliminator varchar(100)) returns @t table (numberval numeric(25), stringval varchar(100), DateVal datetime) as begin declare @i int; declare @c varchar(100); --set @Str = @Str + ',' set @Str = @Str + @Deliminator set @i = 1; set @c = ''; while @i <= len(@Str) begin --if substring(@Str,@i,1) = ',' if substring(@Str,@i,1) = @Deliminator begin insert into @t values (CASE WHEN isnumeric(@c)=1 THEN @c else Null END, rtrim(ltrim(@c)), CASE WHEN isdate(@c)=1 then @c else Null END) set @c = '' end else set @c = @c + substring(@Str,@i,1) set @i = @i +1 end return end ---------------- to call this function,use following... select * from dbo.SplitValues('1,2,3,4,5',',') ------------- this will return following results... items -------- 1 2 3 4 5

No comments:

Post a Comment

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...