How to add new line in sql server 2008 db

Question

hi all,

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"

Answer 1

Hi,

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

Answer 2

Hi SvgSuresh,

Thanks for replaying . What i ment was like this

Roma victa

flat no 30

Dera

Dubai

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

Thanks

Roma victa

Answer 3

For the ease of use, why dont you split the address column in to multiple columns like.

address line 1

address line 2

city

state

country

postalcode

It is a better practice to have it in different columns.

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com

Answer 4

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
(address varchar(100));

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

Roma victa
flat no 30
Dera
Dubai

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

Answer 5

There 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


My blog