Solved

Forcing a new line

Posted on 2002-05-21
12
1,029 Views
Last Modified: 2008-03-06
I am looking for a way in an SQL statement to force a new line in a varchar field.
0
Comment
Question by:Garfy
[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
  • 6
  • 5
12 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 100 total points
ID: 7025863
Try concatenating CHAR(10) where you want the new line, for example:

SELECT SUBSTRING(varchar_col, 1, 5) + CHAR(10) + SUBSTRING(varchar_col, 6, 10)
FROM ...

Or

SELECT varchar_col1 + CHAR(10) + varchar_col2
FROM ...

etc..
0
 
LVL 1

Expert Comment

by:falconew
ID: 7025880
ping
0
 
LVL 1

Author Comment

by:Garfy
ID: 7026183
Sorry I should have said before but I am trying to insert a string with compulsory line breaks between words..

Eg.

"Annual General Meeting held on 1/1/2000 at 2:00pm at Henry's place"

Should go in like
"Annual General Meeting
 held on 1/1/2000
 at 2:00pm at Henry's place"
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 1

Author Comment

by:Garfy
ID: 7026256
CHAR(10) puts in an unknown character...

here is the first part of the SQL statement using Scott's answer

Update [Company]
SET
 [Comments] = 'Regular meeting date : BI MONTHLY '
+ CHAR(10)
+ 'Meeting time : 1.30PM '
+ CHAR(10)
+ 'Meeting place : Otway Community Services '
+ CHAR(10)
+ 'Annual General Meeting: '
+ CHAR(10)
+ '03091997 '
+ CHAR(10)
+ 'at 1.00PM '
+ CHAR(10)
+ 'at SENIOR CITIZENS HALL'
0
 
LVL 1

Expert Comment

by:falconew
ID: 7026341
what do you mean char(10) puts in an unknown character?
Why do you want to force a new line in a varchar field?
If I know your requirement, probably we can find another way to solve the problem.

Regards
0
 
LVL 1

Author Comment

by:Garfy
ID: 7028517
I am trying to force the varchar field to have multiple lines but where I want them.  That way when I bring them into a Crystal Report they will be formatted properly.  I cannot extend the database to include more fields and I want to make the report do as little work as possible.  The original data was brought across from an AS400.  But when it was brought across they forgot the AGM data.  The meeting data was brought across though and it has been placed in multiple lines in the way that I am trying to achieve with the AGM data.  I know I can press Ctrl Enter on the database, but I am automating the system (as there are many records) by using VB.  Hence the requirement for an SQL statement.

Also, when I use Char(10) or Char(13) SQL (2000) puts in a little square box instead of a line break, meaning it does not recognise the character.
0
 
LVL 1

Expert Comment

by:falconew
ID: 7028650
When I query the table in query analyzer which contain the char(10) it show up correctly. The square box appear when the result viewed in grid.
I think it depends on the query tools to recognize the character.
Is it a problem when you run it on crystal report using this character?
0
 
LVL 1

Author Comment

by:Garfy
ID: 7036277
hmmm, older data that was brought across from the AS400 has forced line breaks that you cannot see but can see the effect of in the grid of SQL.  The square character does not give me the effect of a new line...
0
 
LVL 1

Expert Comment

by:falconew
ID: 7036296
Yes, the square character does not give the effect in the grid of SQL. But it has an effect when you set the query analyzer to text instead of grid.
Do you mean grid in SQL analyzer or grid control in VB?
0
 
LVL 1

Author Comment

by:Garfy
ID: 7037911
Grid in SQL Server....  after opening the table and viewing all rows...
0
 
LVL 1

Expert Comment

by:falconew
ID: 7038355
why don't give a try using the Query analyzer and set it to the text display instead of the grid..
0
 
LVL 1

Author Comment

by:Garfy
ID: 7038406
Finally got it to work...  thanks....
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

710 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