?
Solved

LF and CR in string fields?

Posted on 2003-03-31
7
Medium Priority
?
632 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
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
Industry Leaders: 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

621 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