February 16, 2005 at 12:42 pm
I'm importing text from at table with text which is unlimited. Som fields have as much as 18000 characters which exceeds MSSQL query analysers output of 8192 characters per column. I need to extract all the text in chuncks but don't know the proper tsql statements to use.
February 16, 2005 at 1:58 pm
Here is a simple method. You can specify whatever length works best for you.
DECLARE @LongText AS varchar(8000)
SELECT @LongText = '1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-19-20-21-22-23-24-25-26-27-28-29-30-31-32-33-34-35-36-37-38-39-40-41-42-43-44-45-46-47-48-49-50-51-52-53-54-55-56-57-58-59-60-61-62-63-64-65-66-67-68-69-70-71-72-73-74-75-76-77-78-79-80-81-82-83-84-85-86-87-88-89-90-91-92-93-94-95-96-97-98-99-100'
SELECT SUBSTRING( @LongText, 1, 99)
SELECT SUBSTRING( @LongText, 100, 99)
SELECT SUBSTRING( @LongText, 199, 99)
I wasn't born stupid - I had to study.
February 16, 2005 at 6:53 pm
I don't quite get it. the columns I'm interested in extracting chunks of text from is textinfo. the following statement gives the answer to the select statement and an answer for no column name: 1-2-3- etc :
select ovr_id, textinfo, adr_id from dbo.ovriga
DECLARE @LongText AS varchar(8000)
SELECT @LongText = '1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-19-20-21-22-23-24-25-26-27-28-29-30-31-32-33-34-35-36-37-38-39-40-41-42-43-44-45-46-47-48-49-50-51-52-53-54-55-56-57-58-59-60-61-62-63-64-65-66-67-68-69-70-71-72-73-74-75-76-77-78-79-80-81-82-83-84-85-86-87-88-89-90-91-92-93-94-95-96-97-98-99-100'
SELECT SUBSTRING( @LongText, 1, 99)
I'm obviously not phrasing my question properly
February 17, 2005 at 8:58 am
I'm not clear as to what you want to do. I assume you've got data in a table called dbo.ovriga, and you want to look at all the text data.
First of all, Query Analyzer is not a good tool for this. However, the following script will display all of the text for each row in the table. Before you run it, I would click on the Tools | Options menu item, then the Results tab. Select Results to text, and clear the "Print Column Headers" checkbox.
This script will arbitrarily break the text at fixed points (see @blocksize, which you can change), so words may be split across lines, but at least you can see all of the text.
-- select ovr_id, textinfo, adr_id from dbo.ovriga
DECLARE @ovr_id int
DECLARE @val varbinary(16), @textlen int
DECLARE @blocksize int, @bytesRemaining int, @offset int
SET @blocksize = 100
SET NOCOUNT ON
DECLARE c_Keys CURSOR FOR
SELECT ovr_id
FROM dbo.ovriga
OPEN c_Keys
FETCH NEXT FROM c_Keys INTO @ovr_id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT Replicate('-', 100)
PRINT @ovr_id
PRINT Replicate('-', 100)
PRINT ''
SELECT @val = TextPtr(textinfo)
FROM dbo.ovriga
WHERE ovr_id = @ovr_id
SET @textlen = (SELECT Datalength(textinfo) FROM dbo.ovriga WHERE ovr_id = @ovr_id)
SET @bytesRemaining = @textlen
SET @offset = 0
WHILE @bytesRemaining > 0
BEGIN
IF @bytesRemaining < @blocksize
SET @blocksize = @bytesRemaining
READTEXT dbo.ovriga.textinfo @val @offset @blocksize
SET @offset = @offset + @blocksize
SET @bytesRemaining = @bytesRemaining - @blocksize
END -- WHILE
FETCH NEXT FROM c_Keys INTO @ovr_id
END -- WHILE (Cursor Loop)
February 17, 2005 at 5:29 pm
What I'm trying to do is get an output with column headears Ovr_id, textinfo, and adr_id, but I maximun size of the textinfo is 2046 characters. The results will be imported into another database where the text field is limited to 2046 characters. I need the other ovr_id and adr_id to link the text to the correct id and address.
April 2, 2005 at 9:20 am
And the solution is!:
select textinfo from ovriga
where
(DATALENGTH(dbo.OVRIGA.textinfo) between 2047 and 4092)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply