January 30, 2012 at 12:51 pm
table1
(
filename varchar(20),
Extension varchar(200)
Description text
)
here iam having the 5 column i want slipt function
determine by comma
the output should be in one insert function i want to insert data more thant one like this
FileName Extension Description
df .doc fgdsf
df .jpg fgdsf
i created function
CREATE FUNCTION [dbo].[Split1](@String varchar(8000),@string1 varchar(8000),@string2 varchar(8000), @Delimiter char(1))
returns@tempTable TABLE ([filename] varchar(8000), extension varchar(8000),[description] varchar(8000))
as
begin
declare @idx intdeclare @idx1 intdeclare @idx2 int
declare @slice varchar(8000)declare @slice1 varchar(800)declare @slice2 varchar(8000)
select @idx = 1select @idx1 = 1select @idx2 = 1
if len(@String)<1 or @String is null return
while @idx!= 0 and @idx1!= 0 and @idx2!= 0
begin
set @idx = charindex(@Delimiter,@String)set @idx1 = charindex(@Delimiter,@string1)set @idx1 = charindex(@Delimiter,@string2)
if @idx!=0 and @idx1!= 0 and @idx2!= 0beginset @slice = left(@String,@idx - 1)set @slice1 = left(@String1,@idx1 - 1)set @slice2 = left(@String2,@idx2 - 1)end
elsebeginset @slice = @Stringset @slice1 = @String1set @slice2 = @String2endif(len(@slice)>0) and (len(@slice1)>0)and (len(@slice2)>0)
insert into @tempTable ([filename], extension,[description]) values(@slice,@slice1,@slice2)
set @String = right(@String,len(@String) - @idx)set @String1 = right(@String1,len(@String1) - @idx1)set @String2 = right(@String2,len(@String2) - @idx2)if len(@String) = 0 breakelse if len(@String1)=0 breakelse if len(@String2)=0 break
end
return
end
GO
i found one error plz solve my problem
January 30, 2012 at 1:09 pm
You really should not do this in a loop. Take a look at the link in my signature about a string splitter. It will blow the doors of any kind of looping construct for this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2012 at 1:18 pm
Sean beat me to it...:-P
A couple things here. First, there's some formatting issues with the code you posted. If you copy and paste that to a new query window you'll get syntax errors. Second, it's hard to tell what you're trying to accomplish based on the criteria you gave. Sample data, expected output, and create table scripts (ddl) goes a long way in helping someone help you. Some people will just skip over your post if you don't supply these. Ideally I should be able to copy and paste the code you post to a new query window on my machine and have everything in front of me that I need to work on your question (sample data, expected output, ddl sripts etc.). Lastly, although what you're doing above will probably work for what you're trying to accomplish, I've created splitter functions that do almost exactly what it looks like you're trying to do in the past with success, there's probably a better way to do it.
Here's a couple links that will help you. The first shows how to better post your question so you get an answer in a timely manner without having people guess, which you can use going forward. The second is an article that I believe will help you tackle the problem you have with splitting your columns.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
January 30, 2012 at 2:16 pm
January 31, 2012 at 12:15 pm
i found the solution for my problem anyway thank u every one for sending our valuable time for me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply