Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

Creating a Coma Parsing Function Using SQL Server

Author: DevASP

This article is about how you can parse a comma string in sql server using a user defined function.

  • Steps you will do. 
  • Open sql server ‘Enterprise Manager’ and create a new user defined function as below.

 

CREATE FUNCTION CommaParsing (@SEARCH VARCHAR(8000))

RETURNS @retisoc_tab table (isocs varchar(8000)) AS

BEGIN

declare @pos int,@isoc varchar(8000),@strlen int

declare @isoc_tab table (isocs varchar(8000))

set @search = ltrim(rtrim(@search))

while len(@search) >=0

begin

set @strlen = len(ltrim(@search))

set @pos = charindex(',',@search,1)

if @pos=0 and @search is not null

begin

set @search = ltrim(rtrim(@search))

insert into @isoc_tab values(@search)

insert into @retisoc_tab select * from @isoc_tab

return

end

set @isoc = substring(rtrim(ltrim(@search)),1,@pos-1)

insert into @isoc_tab values(@isoc)

set @search = ltrim(right(@search,@strlen-@pos))

end

return

END

 

  • Now create a store procedure to test this use define function as below.

 

CREATE PROCEDURE PrintValues

@ids varchar(50)

AS

BEGIN

SELECT * FROM CommaParsing(@Ids)

END

GO

 

  • Now open your Query Analyzer and run the store procedure. Pass a string type parameter as below.

 

 

PrintValues '2,1,4,5,6,7,8,19,12,3,16,300'

 

See the output in query analyzer . It will show you a table with one column.

 

 

 

Article Comments
Thanks for giving the parsing function using SQL server.

Posted on 9/27/2010 12:59:18 AM by annuaire jeux de casino en ligne

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< ADO.Net Data Providers, A Beginner’s Guide

Disclaimer - Privacy
© 2002-2017 DevASP.net