Garfy
asked on
Forcing a new line
I am looking for a way in an SQL statement to force a new line in a varchar field.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ping
ASKER
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"
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"
ASKER
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'
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'
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
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
ASKER
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.
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.
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?
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?
ASKER
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...
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?
Do you mean grid in SQL analyzer or grid control in VB?
ASKER
Grid in SQL Server.... after opening the table and viewing all rows...
why don't give a try using the Query analyzer and set it to the text display instead of the grid..
ASKER
Finally got it to work... thanks....