.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

How to split a string

Posted By:Kemal AL GAZZAH       Posted Date: January 26, 2013    Points: 25    Category: SQL Query    URL: http://www.dotnetspark.com  

This code splits a string with separator into a Table
 

One Colleague asked for a code which splits a string having a separator into a Table
Here is the code:
--usp_split_string '10;25;25;78;45;17;15;7895;781;;000;78;9;4454;xxx;iure;789;-;;;;-;',';'
alter proc usp_split_string(@str nvarchar(1000),@sep nvarchar(1))
as
--Kamel Gazzah
--https://www.facebook.com/kmlgzh
--http://www.blogger.com/blogger.g?blogID=7339891687039262305#overview/src=dashboard
--26/01/2013
begin
--declare @str as nvarchar(100)
declare @str1 as nvarchar(1000)
declare @istr as nvarchar(1000)
--declare @sep as nvarchar(1)
declare @i as integer

--set @str='10;25;25;78;45;17;15;7895;781;;000;78;9;4454;xxx;iure;789;-;;;;-;'
--set @sep=';'

if exists(select 1 from sysobjects where name='tab_temp') drop table tab_temp
create table tab_temp(id int identity(1,1),str nvarchar(1000))

set @str1=@str
set @i=2
while @i>0 --and len(@str1)>1
begin
set @i=charindex(@sep,@str1,1)
if @i>0
begin
set @istr=substring(@str1,1,@i-1)
insert into tab_temp(str) values(@istr)
set @str1=substring(@str1,@i+1,len(@str1))

end
end
insert into tab_temp(str) values(@str1)
--select @i
select * from tab_temp

end




     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend