CODEWITHSUNDEEP
sqlsql-servert-sqlnewline
user1506097
user1506097

Reputation: 173

New line in TSQL

I am getting records from database as comma separated. I am getting contact titles as:

greg w.workcerf, ashir ali, asdddfgjk

This is comma separated has been defined in SQL function getCommaListTitle()

What i want is to get these record on new lines as

greg w.workcerf,
ashir ali,
asdddfgjk

Any idea about what should i use in sql function instead of ','

Upvotes: 17

Views: 38842

Answers (3)

Johnno Nolan
Johnno Nolan

Reputation: 29667

Append after the comma in getCommaListTitle, CHAR(13) + CHAR(10) for a new line

CHAR(13) is a new line char and CHAR(10) is a line feed.

See http://msdn.microsoft.com/en-us/library/ms187323.aspx

Upvotes: 48

Paul
Paul

Reputation: 1066

use the replace function

replace(field, ',', ',' + char(13)+char(10)

...however DO NOT do this in your database, database is about DATA and of course it 'should' be presented in some form... but starting with a line break, and finally you'll end with something like:

SELECT @s = '<tr><td>' + firstname + '</td><td>' + substr(lastname, 1, 30) + '</td></tr>'
FROM ....

RETURN '<table>' + @s + '</TABLE>'

and that is not to route to choose grasshopper

Upvotes: 3

yogi
yogi

Reputation: 19619

You should do this in your front end, like on data access layer or may be at presentation layer because your application could be any one a web app or a window app and in both there's different in new line syntax like in web we use <br/> tag whereas in window we use /n.

Upvotes: 4

Related Questions