Total Pageviews
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
Subscribe to:
Post Comments (Atom)
Blog Archive
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...
-
All we need to use the Custom paging in grid view without object data source. Here i am going to explain my code which is used in custom pa...
-
One of the common requests in ASP.NET is to submit a form when visitor hits an Enter key. That could be a case if, for example you want to m...
-
using System.Data; using System.Data.SqlClient; using System.Text; using System.IO; using iTextSharp.text; using iTextSharp.text.pdf; ...
No comments:
Post a Comment