Replacing characters in a field in a SPROC

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)

Replacing characters in a field in a SPROC

Author

Topic

sbushway
Starting Member

18 Posts

Posted - 2005-06-14 : 15:50:56

Hi,
I've got a simple stored procedure that retrieves a group of records from a SQL Server table.

One of the fields might contain two right square brackets (]]). If that happens, I don't want to display the brackets to the user. I want to replace the brackets with the line-feed character, Chr(10).

The query in my SPROC is a bit long, so I'll shorten it down for simplicity's sake:

SELECT FName, LName, DateChanged, Comments
FROM AddressChangeLog

The "Comments" field is the field which might contain the square brackets.

This is essentially what I'm thinking it'll be (forgive how crude and completely incorrect this will probably be ...)

if(CHARINDEX(']]', Comments) = 0 return Comments
else return Replace(Comments, ']]', 'Chr(10)')

I might be able to find a way to replace the square brackets in my program (VB.NET web app, in my case), but I was hoping that there'd be a way to do it in the SQL.

Thanks in advance!
Suzanne

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-14 : 15:55:57

Select fName, LName, DateChanged, Comments = Replace(Comments,']]',char(10))
From AddressChangeLog

Corey