Tuesday, February 21, 2012 9:20 AM
I have an address column. some data from the address has to come in the next line. i wonder if there are any new line charector that i can put directly to the database to achive this (like '\n'). The datatype of the column is "text"
Tuesday, February 21, 2012 10:00 AM
Please try with the below code
DECLARE @TEX TABLE(ID INT,MYADDRESS TEXT) INSERT INTO @TEX VALUES(1,'WISH YOU HAPPY BIRTHDAY') SELECT REPLACE(CONVERT(VARCHAR(MAX),MYADDRESS), ' ', CHAR(13)) FROM @TEX
- Edited by svgSuresh Tuesday, February 21, 2012 10:03 AM
Tuesday, February 21, 2012 2:38 PM
Thanks for replaying . What i ment was like this
flat no 30
but it is storred in the database Roma victa flat no 30 Dera Dubai
is there any way that i can mannually put enter character in it
so that while showing it in .net it is shown in diffrent lines insted of one
- Edited by roma_victa Tuesday, February 21, 2012 2:40 PM
Tuesday, February 21, 2012 2:48 PM
For the ease of use, why dont you split the address column in to multiple columns like.
address line 1
address line 2
It is a better practice to have it in different columns.
Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
Tuesday, February 21, 2012 4:50 PMAnswerer
Use a tool such as SQL Server Management Studio and execute the following inserting the next line character char(13) in as needed:
CREATE TABLE Address
INSERT Address (Address) VALUES (
'Roma victa' + char(13) + 'flat no 30' + char(13) + 'Dera' + char(13) + 'Dubai');
Now if you execute:
SELECT * FROM Address
with the Query Editor in "Results to Grid" mode you get:
Roma victa flat no 30 Dera Dubai
But if you switch to "Results to Text" mode you get
flat no 30
This demonstrates that the next line character is stored in the database. Your problem now is how to get your presentation program to read and implement the next line.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
Tuesday, February 21, 2012 4:55 PMModeratorThere is no way to insert CHAR(13) directly using Edit 200 top rows. However, you can always use INSERT / UPDATE command to insert the char(13). Also, you will not see it using output to grid option of the query results.
For every expert, there is an equal and opposite expert. - Becker's Law