Adding a carridge return to a nvarchar value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Adding a carridge return to a nvarchar value

Author

Topic

adjones1980
Starting Member

36 Posts

Posted - 2007-06-19 : 06:49:10

Hello,

I need to record posted user comments in an nvarchar field so that when I databind it to an ASP control it would look something like this:

JSMITH(01/01/2007): added a comment.
JSMITH(01/01/2007): added another comment.
JSMITH(01/01/2007): added yet another comment.

I know that this should probably be in a seperate child table and the comments posted seperate records, but just humour me for now!

Check my code below and I have highlighted where the carridge return should go.

CREATE PROCEDURE procAddComment

(
	@CP int,
	@Comment nvarchar(1000),
	@UserName nvarchar(50)
)

 AS

DECLARE @CPID nvarchar(50)
DECLARE @CurrentComments nvarchar(2000)

/* Get current data */
SELECT @CPID = CPID, @CurrentComments=UserComments FROM tbl_tmp_CP WHERE CP = @CP

/* Update the comment with new stuff */
UPDATE [dft-traffic-v1].[dbo].[tbl_tmp_CP]
SET [UserComments] = @CurrentComments + 
<#### carridge return in here####> + 
@Username + '('+ FORMAT(GETDATE(),"dd/mm/yyyy") + '): ' + @Comment
WHERE CPID = @CPID

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-19 : 06:54:16

Do you want a carriage return or a carriage return + line feed.

SET [UserComments] = @CurrentComments +
'
' +
@Username + '('+ FORMAT(GETDATE(),"dd/mm/yyyy") + '): ' + @Comment
WHERE CPID = @CPID

would give a crlf
for a cr char(13)
for a lf char(10)

char(13) + char(10) should do the same as the above but has a few problems sometimes if you are accessing databases with different collations.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adjones1980
Starting Member

36 Posts

Posted - 2007-06-19 : 07:27:48

Ah... I tried Chr(10) + Chr(13) before but can now see my mistake there.

Thanks for that!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources