August 28, 2008 at 4:18 am
Hi,
I have a problem with my procedure.
The work is I will be reciving a comma separeted text value in a variable and i have segregate the comma seperareted .
So i had made the procedure. The procedure does that but the moment it reaches the 1000th row problem occurs and it just take the first 2 characters and replace it with the rest of the values after 1000 rows.
Please help.
I am not able to debgug as to where the problem is ?:
CREATE PROCEDURE proc_Function_Explode
(@Delimiter VARCHAR(10),
@Value TEXT )
AS
BEGIN
DECLARE @temp_Value INT ,
@TotalLength INT ,
@T_Length INT
SELECT @TotalLength = ISNULL(DATALENGTH(@Value),'')
IF @TotalLength = ''
BEGIN
RETURN
END
-- Table to store the values after seggregating the values from the text field.
CREATE TABLE #Temp
(Item_Id INT NOT NULL)
-- Temp table to store the Total values for further manipulations for the text field.
CREATE TABLE #test
(Next_Expr text)
-- Taking all the data into the temporary table for all the
INSERT INTO #test
VALUES (@Value)
IF (@Delimiter = ',') -- Begin for delimiter ',' for comma seperated.
BEGIN
-- Populating the first value from the text variable .
SELECT @temp_Value = CONVERT(INT , (CONVERT(VARCHAR(100),(SUBSTRING(@Value , 1, (PATINDEX('%,%' ,@Value)-1))))))
--Populating the variable with the lenght of the variables.
SELECT @T_Length = LEN(@temp_Value)
-- Begin of the While Loop
-- While loop run so that it populates one by one value in the table.
WHILE (@TotalLength - @T_Length > 0)
BEGIN
-- Inserting the value in the #temp table from the variable.
INSERT INTO #Temp
SELECT @temp_Value
-- Updating the table with the next set of values to be extracted.
UPDATE #test
SET Next_Expr = SUBSTRING(Next_Expr, (PATINDEX('%,%' ,Next_Expr))+1,@TotalLength-(len(@temp_Value)+1))
--select * from #test
-- Taking into the variable the values into the temp variable.
SELECT @temp_Value = CASE WHEN (PATINDEX('%,%' ,Next_Expr) <> 0)
THEN CONVERT(INT , (CONVERT(VARCHAR(100),(SUBSTRING(Next_Expr , 1, (PATINDEX('%,%' ,Next_Expr)-1))))))
ELSE CONVERT(int ,(CONVERT(VARCHAR(100),Next_Expr )))
END
FROM #test
-- IF (@temp_Value = 12)
-- BEGIN
-- SELECT @temp_Value ,@T_Length
-- Select * from #test
-- select * from #Temp
--
-- break
-- END
-- Calculation for the while loop to run.
SELECT @T_Length = @T_Length+Len(@temp_Value)+1
END -- End of while loop.
SELECT * FROM #Temp
END -- End of If.
END
go
for testing the parameters are
EXEC proc_function_explode ',', '1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567'
please help...
August 28, 2008 at 6:59 am
Check out this article, http://www.sqlservercentral.com/articles/TSQL/62867/. It contains a great method for parsing a string in the Stepping through Characters section about halfway down.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 29, 2008 at 7:03 am
The article is good.
But it did not had the solution of my problem. I have to receive a text field in which you cannot add any extra ',' anywhere .
I have asked the front end people to send the parameter to add a comma at the last.
I have the problem that when it is run in text field after certain time or point it just take 2 values from the values and dispalys.
, which is incorrect.
eg.
I have a values '121212,1213231,3123,23,4444,4444,4444,444,4444,444,'
i want it to be displayed like
121212
1213231
3123
23
4444
4444
4444
444
4444
444
but it displays
121212
1213231
3123
23
4444
44
44
44
44
44
Now I am in jinx where is the problem in my code , where is it breaking?
August 29, 2008 at 9:50 am
harsha.bhagat1 (8/29/2008)
I have to receive a text field in which you cannot add any extra ',' anywhere .
If you are receiving the data as a parameter why can't you declare a new variable to add the beginning and ending commas? Like this:
Declare @text varchar(8000)
Set @text = ',' + @parameter + ','
Then you process the @text variable or you can just do this:
Select
Substring(',' + @text + ',', N+1, Charindex(',', ',' + @text + ',', N+1) - n -1)
From
tally
Where
N <= Len(',' + @text + ',') AND
SUBSTRING(',' + @text + ',', N,1) = ',' And
Charindex(',', ',' + @text + ',', N+1) > 0
order by
n
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 29, 2008 at 5:31 pm
This might give you a start, it was posted here on SCC but by whom my memory fails me.
DECLARE @string VARCHAR(50)--must be sized large enough for given string
DECLARE @Find AS INT
DECLARE @Char AS VARCHAR(5)
DECLARE @First AS INT
DECLARE @Rep AS VARCHAR(5)
DECLARE @position AS INT
-- Initialize the current position and the string variables.
SET @position = 1
SET @First = 1
SET @Rep = ''
SET @string = '22,4444,444,66,44,4,5,55,5555,55'
--SET @string = '~~F~T~G'
WHILE @position <= DATALENGTH(@string)
BEGIN
SET @Find = (SELECT ASCII(SUBSTRING(@string, @position, 1)))
/*--this is the ASCII value for the string separator in this case a comma
it may be any character */
IF @Find <> 44 BEGIN
SET @Char = (SELECT CHAR(ASCII(SUBSTRING(@string, @Position, (@position - @First) +1))))
SET @Rep = @Rep + @Char
END
ELSE
BEGIN
PRINT 'Rep found*' + @Rep + '*' --used only during testing
SET @First = @position
SET @Rep = ''
END
IF @position = DATALENGTH(@string)
BEGIN
IF @position - DATALENGTH(@string) > 0
BEGIN
SET @Char = (SELECT CHAR(ASCII(SUBSTRING(@string, @Position, (@position - DATALENGTH(@string))) )))
SET @Rep = @Rep + @Char
END
PRINT 'Last Rep found*' + @Rep + '*'--only used during testing
END
SET @position = @position + 1
END
For the string defined above the result is
Rep found*22*
Rep found*4444*
Rep found*444*
Rep found*66*
Rep found*44*
Rep found*4*
Rep found*5*
Rep found*55*
Rep found*5555*
Last Rep found*55*
August 30, 2008 at 9:48 am
I gotto go with Jack on this one... use a Tally table instead of a While loop. In fact, here's an article on what a Tally table is and how it can be used to replace While loops...
http://www.sqlservercentral.com/articles/TSQL/62867/
The last part of that article tells you how to do a "split".
For much more detail on how to do both simple split's and how to pass arrays as parameters, here's a different article...
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2008 at 3:45 am
My problem still exists.
I cannot change the the datatype of my variable as there will be a data comming more that 8000.
If you test the values what i have provided then you can see that its breaking after 1000. I get incorrect resultset after 1000 records.
I am not able to understand where the code is breaking or where is the error happening.
Using the tally table can help me with the varchar datatype values but not with the text values.
If you execute the test script given below you can understand my problem.
EXEC proc_function_explode ',' , '1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,'
The procedure is provieded in the first issue.
Thanks in advance everybody for the help..
September 1, 2008 at 4:34 am
If you are using sql server 2005, then use VARCHAR(MAX) datatype instead of text datatype.
if you are using sql server 2000, then you have to first split the string to a variable of length less than 8000. Then split the string again for the specified delimiter.
September 1, 2008 at 5:56 am
Of course you can use a tally table with text values.
This is ugly because you can't use CHARINDEX() with text values and it assumes that the max length of a substring is 12 characters, but it works...and if something works then it can usually be made to look nicer and work better...
[font="Courier New"]CREATE PROCEDURE proc_Function_Explode_New
(@Delimiter CHAR(1),
@String TEXT)
AS
SELECT
CASE WHEN PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, number+1, 12)) > 0
THEN SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END,
PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END, 12))-1)
ELSE SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END,
DATALENGTH(SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END, 12))) END
FROM Numbers
WHERE number <= DATALENGTH(@String)
AND (SUBSTRING(@String, number, 1) = @Delimiter
OR number = 1)
ORDER BY number
RETURN [/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 1, 2008 at 9:40 am
Its not the solution..
There is the problem what i found after working on it more today.
I am not able to split my text field. Today I found that the substring is not wrking properly for which my data is not comming properly..
Since there was a suggestion and I think it was you , but it isnot working with the tally table..
Or I think may be the code is not full..
I am still working on it. ..
On the site I found handling text field. So iread and posted my problem
The other one is I had asked as I was notr aware that there was an article over here regarding the text handling..
Sorry if I had given any trouble..
SUBSTRING works with the text datatype provided that the returned part of the string doesn't exceed VARCHAR(8000). The trick is to return manageable chunks from the text data.
Do you have a tally table? Here's code to generate it...
[font="Courier New"]CREATE PROCEDURE [dbo].[MakeNumbersTable]
-- Courtesy of Jeff Moden, SSC
AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Numbers]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Numbers
--===== Create and populate the Tally table on the fly
SELECT TOP 1000000
IDENTITY(INT,1,1) AS number
INTO dbo.Numbers
FROM MASTER.dbo.syscolumns sc1,
MASTER.dbo.syscolumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_numbers_number PRIMARY KEY CLUSTERED (number)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Numbers TO PUBLIC
GO
[/font]
My code works with the test data you posted - note that the test data has adjacent commas in several places.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 1, 2008 at 10:07 am
It really works..:w00t:
Thank you soooooooooooooooooooooooooooo much.. :w00t::w00t::w00t:
Thank you so much..
:):laugh:
September 1, 2008 at 10:15 am
Here's another slightly different version which is easier to test and accounts for leading and trailing commas:
[font="Courier New"]CREATE PROCEDURE proc_Function_Explode_New2
(@Delimiter CHAR(1),
@String TEXT)
AS
DECLARE @StringLength INT
SET @StringLength = DATALENGTH(@String)
CREATE TABLE #Temp (
[RowID] [int] IDENTITY (1, 1) NOT NULL,
number INT
) ON [PRIMARY]
-- If the string doesn't start with a delimiter, then put the first element into the table
IF NOT SUBSTRING(@String, 1, 1) = @Delimiter
INSERT INTO #Temp (number) VALUES (0)
INSERT INTO #Temp
SELECT number
FROM Numbers
WHERE number <= DATALENGTH(@String)
AND SUBSTRING(@String, number, 1) = @Delimiter
ORDER BY number
-- If the string doesn't end with a delimiter, then put the last element into the table
IF NOT SUBSTRING(@String, @StringLength, 1) = @Delimiter
INSERT INTO #Temp (number) VALUES (@StringLength+1)
SELECT SUBSTRING(@String, a.number+1, b.number-a.number-1)
FROM #Temp a
INNER JOIN #Temp b ON b.RowID = a.RowID + 1
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 1, 2008 at 10:24 am
Thanks a million..
It works..
Thank you so much..
🙂
September 2, 2008 at 2:10 am
harsha.bhagat1 (9/1/2008)
Thanks a million..It works..
Thank you so much..
🙂
You're welcome Harsha. Don't ya just love a happy customer? 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 2, 2008 at 2:27 am
Yep,
But Thank you so much..:)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply