Tally text - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Tally text

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-10-10 : 11:43:27

I am using this method: http://www.sqlteam.com/item.asp?ItemID=2652
So it looks like this:
SELECT Author,
NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word
FROM Tally, Quotes
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0

My problem is that Phrase in my case is a TEXT and not Varchar(8000).
I see the mighty Rob once battled with this issue: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10245&whichpage=1
But I dont know if he ever came to grips with it.

This tally query is already complex enough in my (one-off) script that will run as part of a data conversion. I'm not quite sure what's the most efficient/easy way to deal with the TEXT issue here without making my head spin. I'm thinking there's gonna have to be a bit of READTEXT going on as well as an array of varchar(8000)'s.

Anyone alert and awake enough today to help with this one? I know the answer will probably be ugly. I don't mind. As long as it's not dog slow.

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 12:12:01

I'm as good as certain that there is a SPLIT function for TEXT datatypes somewhere on SQL Team - a search via Google might help

site:SQLTeam.com CSV split ...

Kristen

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-10-10 : 12:13:45

yeah there is but it suffers from the varchar 8000 limitation

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 12:15:42

Well blow me down!

Found it via the "Frequently Given Answers" post at the top of "New to SQL Server" forum

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

which has a link to "Split functions" :

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

and that in turn has a link to a "TEXT datatype splitter" :

http://weblogs.sqlteam.com/davidm/archive/2003/12/12/655.aspx

Kristen

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-10-10 : 12:15:57

plus like I said,

this statement works perfectly as it stands:
SELECT Author,
NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word
FROM Tally, Quotes
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0

other than the fact that it cant handle it if the Phrase column is TEXT instead of varchar(8000)

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-10-10 : 12:16:42

woops!
thanks

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 12:21:53

replying to your own thread!

Kristen

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-10-10 : 12:22:41

it seems kristen is surreptitiously setting up his own site within a site
well done mate

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-10-10 : 12:36:00

quote:
Originally posted by Kristen

replying to your own thread!

Kristen

What does that mean?

Another thing:

I'm not quite sure how to merge this:

SELECT Author,
NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word
FROM Tally, Quotes
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0

with this:

CREATE FUNCTION RowParser
(
@Text TEXT,
@Separator VARCHAR(3)
)
RETURNS TABLE
AS
RETURN
(
SELECT ID ,
SUBSTRING(@Text, ID ,
CASE SIGN(CHARINDEX(@Separator, @Text,ID )-ID )
WHEN -1 THEN
CASE PATINDEX('%,%', SUBSTRING(@Text, ID , ABS(CHARINDEX(@Separator, @Text,ID )-ID )))
WHEN 0 THEN DATALENGTH(@Text)-ID +1
ELSE PATINDEX('%,%', SUBSTRING(@Text, ID , ABS(CHARINDEX(@Separator, @Text,ID )-ID ))) -1
END
ELSE ABS(CHARINDEX(@Separator, @Text,ID )-ID )
END) AS Data
FROM Tally
WHERE ID BETWEEN 1 AND (DATALENGTH(@Text) + DATALENGTH(@Separator))
AND ((SUBSTRING(@Text,ID -DATALENGTH(@Separator),DATALENGTH(@Separator)) = @Separator AND ID > 1) OR ID = 1)
)
GO

Obviously, replacing "Phrase" in the 1st query with a call to RowParser with "Phrase" as one of the parameters wont do the trick.
I'm now in a situation where I am using the tally table twice.

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 12:47:25

In essence I think you need to Cursor around the Quotes table getting the value for the Phrase column in each row into a variable (e.g. @Phrase) and passing it to RowParser :

INSERT INTO MyTable
SELECT ID, Data
FROM RowParser(@Phrase, ',')

Kristen

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-10-11 : 12:24:00

Th only way I could get it to work was to not create the udf but to rather embed the code that would have been in the udf into the cursor loop. The reason for this is that the variable that you suggested would not be possible because you cannot declare a variable as TEXT. But the cursor is doing what I feared: running slow and maxing out the processor.

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 13:34:54

"you cannot declare a variable as TEXT"

Good point

"the cursor is doing what I feared: running slow and maxing out the processor"

BCP the data out, process externally and import agin?

Kristen

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-10-12 : 11:38:04

na, that wont do it.
I've given a good run to try and do this. now i'm giving up.
I think some things are done best and developed faster by going the exe route. So I think I'll call a vb.net exe to take care of this part of the data conversion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources