Converting text to varchar

Question

Post reply

Converting text to varchar

Carol Adams

SSCrazy

Points: 2320
More actions
February 24, 2009 at 6:29 pm

#155324

I have a table that has a TEXT column that I want to use in stored procedure. The stored procedure will not allow me to declare a variable TEXT, so I declared a variable as varchar(8000).
When I do my select into this variable and display the variable, my text is one big line of text. I lost my carriage return line feeds.
How can I convert the TEXT field and maintain them?

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

the carriage returns are still there...are you looking at the data in SSMS in grid mode? grid mode hides CrLf.

in SSMS, hit Control T, then re-run your query.

your data should show your CrLf's.

if you stick the data in a data table or recordset, the CrLf's will be there on the clietn side as well.

HTH

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!

Carol Adams SSCrazy Points: 2320 More actions · Answer 2

What i am actually doing is putting the column into an email message sent via sql from my stored procedure.

I am using HTML as my format.

When i receive the message it is 1 big string....

ab5sr SSCarpal Tunnel Points: 4056 More actions · Answer 3

You may also have to look in SSMS at Query>>Query Options>> Results - Text and change "Maximum number of characters displayed in each column" to 8000. Think that's the max you can put in there.

Lee

Blog --> http://www.texastoo.com

Lowell SSC Guru Points: 323546 More actions · Answer 4

well, if it's displayed as html, CRLF do not show up!

html does not respect whitespace... you need {BR} markups to do that!

(replace curly braces with LT/GT..forum doesn't like real HTML tags)

find and replace CHAR(13) + CHAR(10) with '{BR}' + CHAR(13 C HAR(10) to get the results you are looking for.

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!

Carol Adams SSCrazy Points: 2320 More actions · Answer 5

The REPLACE worked. I had to convert to varchar first then do the replace.

Thanks for you help.

Lowell SSC Guru Points: 323546 More actions · Answer 6

glad we could help.

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!