Using replace to change carraiage returns to

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)

Using replace to change carraiage returns to

Author

Topic

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-12 : 20:40:46

Hi

I need help formatting some text so that it replaces an returns(line carriages) with tag. I will be display this data in HTML. The data is taken from an asp.net textbox. I have tried using the 'replace' statement (see below) to do this but I think i'm doing something wrong. anyway it doesn't work. Can somebody put me in the right direction. Thanks in advance..

CREATE PROCEDURE spGetNotesByJobAndUserID
@JobID int, @UserID int

AS

SELECT Note.NoteID, Backup_UserNotes.BackUp_Read, Note.Note_Description, REPLACE(Note_Description, CHAR(13),' '), Note.[Date], Note.ByWho, Note.FK_Action_Performed, Note.Job_Date,
Note.Start, Note.Finish, Note.FK_UserID, Note.Total_Hours

FROM Note INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Users ON User_Notes.FK_UN_UserID = Users.UserID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID AND Users.UserID = Backup_UserNotes.BackUp_UserID INNER JOIN
Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID

WHERE Users.UserID = @UserID AND
Master_Jobs.JobID = @JobID

ORDER BY
Note.NoteID DESC
GO

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-12 : 21:05:31

Does the following work?

REPLACE(REPLACE(Note_Description,CHAR(13),'<BR>'),CHAR(10),'')

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-02-12 : 21:47:44

I would handle this in your ASP (.NET ). C# or VB is going to be a hell of a lot easier to do this in, then when you decide there are other transformations you want to do (like changing " to " or converting URLs into hyperlinks) you won't be doing horrible nested replaces in your TSQL code.

Damian

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-12 : 22:03:35

Actually there will be nother conversion on this text. I did get it to work using the following:

SELECT Note.NoteID, Backup_UserNotes.BackUp_Read, REPLACE(Note_Description, CHAR(13),' ') as Note_Description, Note.[Date], Note.ByWho, Note.FK_Action_Performed, Note.Job_Date,
Note.Start, Note.Finish, Note.FK_UserID, Note.Total_Hours

I have one other question if I may. This takes care of 'returns' but doesn't take care of white spaces. How could I do the same for whitespace and what is the char number for spaces?

Thanks

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-02-12 : 22:07:07

Why not just use ' ' ?

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources