?
Solved

LF and CR in string fields?

Posted on 2003-03-31
7
Medium Priority
?
631 Views
Last Modified: 2012-05-04
Hi,

I am trying to have LF and CR in my string fields. On Oracle this is fairly straight to implement but I couldn't find a way of generating such data using SQL Server 2000's query analyser.

Best regards,
0
Comment
Question by:m1fcj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 23

Accepted Solution

by:
adathelad earned 200 total points
ID: 8237468
Hi,
You can add these to a string quite easily, using CHAR(<AsciiCode>).

e.g.
DECLARE @pstrString VARCHAR(100)
SET @pstrString = 'Testing ' + CHAR(10) + ' Line Feed'
PRINT @pstrString

or

DECLARE @pstrString VARCHAR(100)
SET @pstrString = 'Testing ' + CHAR(13) + ' Carriage Return'
PRINT @pstrString

Cheers



0
 
LVL 23

Expert Comment

by:adathelad
ID: 8237504
Note that if you do a SELECT on a field that contains these formatting characters, it will probably show as a blank space. But when you write it to screen (e.g. via ASP, VB or the sql PRINT statement like I gave), it does format the string as appropriate (i.e. displays on 2 lines).
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 8238581
yeah should be straight forward

Declare @crlf char(2)
set @crlf = char(13) + char(10)

....

select 'Dear Sir' + @crlf
       + space(10)+ 'With reference to your letter of ...'

 
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:ispaleny
ID: 8238770
CHAR(13)+CHAR(10) is very usable for dynamic SQL.

But this more readable in scripts:

declare @Char varchar(200)
set @Char=
'Line1
Line2
Line3'
0
 

Expert Comment

by:CleanupPing
ID: 9275433
m1fcj:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 9277151
Recommend: Split points between myself and Lowfatspread
0
 
LVL 1

Author Comment

by:m1fcj
ID: 9277976
I have to admit, I haven't checked the replies for a long time. The problem was apparent in an auto-generated SQL code that was generated by a tool. Our tool would recognise CRs and LFs properly and generate the SQL statement with these actually embedded. On the other hand when this SQL statemend was feeded into SQL Server's Query Analyzer, it would promptly try to be helpful and as a result couldn't run the statements or stripped them. We found a solution with modifying our tool but these answers are all valid. As a result I accepted the first answer. Thanks to you all! :)
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question