Working with Text data in T-SQL
-
Hi
We have a table that has a column of type Text. The data in the column is of variable length, the longest about 2500 characters (I'm guessing).
I need to replace square brackets in the text with XML-type tags.
The problem I have, is that T-SQL shows me only the first 256 characters of the column, even though I used the following syntax:
SET TEXTSIZE 8000
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(TextColumn) FROM TableName WHERE bla bla bla
READTEXT TableName.TextColumn @ptrval 0 0
If I look at the data using the application, it shows the full value of the Text string, it's only T-SQL that truncates it.
I also tried doing a SELECT into a variable using CONVERT(VarChar(8000)) to read the value, but the result is the same - only 256 characters are displayed.
Any ideas?
Thanx in advance
Schalk Lubbe
Cape Town, South Africa
-
Are you talking about the result displayed in your query analyzer? Check if you have Maximum Characters Per Column value under Tools-Options- Results tab.
Prasad Bhogadi
www.inforaise.com -
Hi Prasad
Yes, you guesses right, it was within Query Analyser, and your suggestion was spot-on.
Thanks
Schalk
-
just pasting some code that might be helpful: my snippets catalog named this "Find and Replace TEXT/NTEXT" this is really for when you cannot display a TEXT/NTEXT because it's more than 8000/4000 chars, and gets sent to disk instead of treated like a varchar.
--assuming the table has an ID column.
--in this example, i was replacing html:
--href="reviews needed to become a full url http://www.mysite.com/reviews
DECLARE
@reviewid int, @ptr binary(16), @idx int
SET @reviewid = 0
SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body)-1
FROM reviews
WHERE PATINDEX('%href="reviews%',review_body) > 0
WHILE @reviewid > 0
BEGIN
SELECT @ptr = TEXTPTR(review_body)
FROM reviews
WHERE reviewid = @reviewid
UPDATETEXT reviews
.review_body @ptr @idx 13 'href="ttp://www.mysite.com/reviews'
SET @reviewid = 0
SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body)-1
FROM reviews
WHERE reviewid > @reviewid
AND PATINDEX('%href="reviews%',review_body) > 0
END
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply