Returing Text from an Image Data Type Stops at first NewLine

Question

Post reply

Returing Text from an Image Data Type Stops at first NewLine

NorVid Ops

SSC Rookie

Points: 33
More actions
March 13, 2007 at 4:56 pm

#93073

Hello, first time posting here.
Our vendor has large text fields stored as image data types in SQL Server 2000. Typically, we extract data to external apps by streaming the bytes to a string. We've noticed an issue when viewing such data in the Enterprise Manager - Query Analyzer via SELECT queries. To view such data, we've been casting the image type to a binary, then to a varchar, which works fine for most situations. However, the Query Analyzer, and data export systems, appears to stop and truncate the returned data at the first embedded newline. This is understandable for a few reasons, however, we still need to get the data (sans newlines) using the Query Analyzer or export system via SELECT queries. We've tried various newline replacements (such as replacing \13\10 with a space) in SELECT statements within the Query Analyzer without luck. Suggestions? Alternatives?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 1

Newline could also be just CHAR(10) or CHAR(13). Have you tried replacing each one at a time?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NorVid Ops SSC Rookie Points: 33 More actions · Answer 2

I have tried just about every version of replace I could think of including:

REPLACE(CAST(TABLE.FIELD AS binary), '0D0A', '2020')

REPLACE(REPLACE(CAST(CAST(TABLE.FIELD AS binary) AS varchar), CHAR(13), ' '), CHAR(10), ' ')

Feels like this should be any easy fix and I'm overlooking a simple solution, but just can't seem to find it.

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 3

Can you post a line of the troublesome data?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NorVid Ops SSC Rookie Points: 33 More actions · Answer 4

Well, found the problem. It wasn't with NewLines, it was with sizing the binary data type. It just happened to be that NewLines occured at the max default length of binary for each set of data I was testing.

I was casting to binary then varchar(4000), where I should have been casting to binary(4000) then varchar(4000). So casting to binary without a size only retrieved the first 30 chars. Duh! Anyways thanks for your help as it got my brain out of a loop onto a different track.

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 5

Thanks for the feedback, NorVid.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)