Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Problem with the text data column, variable Expand / Collapse
Author
Message
Posted Thursday, August 28, 2008 4:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:39 PM
Points: 446, Visits: 653
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...
Post #560267
Posted Thursday, August 28, 2008 6:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #560375
Posted Friday, August 29, 2008 7:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:39 PM
Points: 446, Visits: 653
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?
Post #561172
Posted Friday, August 29, 2008 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #561329
Posted Friday, August 29, 2008 5:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 5,573, Visits: 24,808
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*


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #561569
Posted Saturday, August 30, 2008 9:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,781, Visits: 31,238
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #561629
Posted Monday, September 1, 2008 3:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:39 PM
Points: 446, Visits: 653
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..
Post #561918
Posted Monday, September 1, 2008 4:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 4:35 AM
Points: 172, Visits: 258
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.
Post #561935
Posted Monday, September 1, 2008 5:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,129, Visits: 13,510
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...

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




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #561965
Posted Monday, September 1, 2008 9:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,129, Visits: 13,510
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...

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


My code works with the test data you posted - note that the test data has adjacent commas in several places.

Cheers

ChrisM


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #562041
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse