Can someone help me use the Replace function properly?

I try this:

SELECT REPLACE('<br />','<br />','vbCrLf'), * FROM Peart3Log

This returns all the records, but includes a 'no name' column filled
with vbCrLf.

Theres a column called [post] within this table that has <br /> tags in
it and I want to replace them with vbCrLf on a select.

TIA,

Jim,

You need use this

SELECT REPLACE(Post,'<br />','vbCrLf') as 'NewPostColumn', * FROM
Peart3Log

You haven't specified the column as the string to be searched. You've
told SQL Server to search for the string '<br/>' in the string '<br/>'
and replace it with 'vbCRLF'.

So really all you're doing essentially is:

Select 'vbCrLf', * From Peart3Log

I presume you don't actually need to update the data in the column -
this is just for presentation purposes right?


HTH

Barry

Jim,

Ah no - you can't use Replace on Text Data Types.

You could convert the Column first - like this...

Select Replace(Convert(Varchar(8000), Post), '<br/>','vbCrLf')

Only problem would be where the Text Column has data that is greater
than 8000 characters?

Barry

[quoted text, click to view]

Yea, just for presentation purposes. However, I was thinking of changing
data on an insert in the same manner. I may have to (see note below as
to why).

The problem I've ran into is in developing a web app and how the data is
displayed using <br /> tags and vbCrLf. It has to do with a datalist
control and it's a really frustrating issue. I can't assume those
viewing this group know about asp.net and vb.net so I try not to put any
of that into my questions here (although it would be nice if I could).
The question I posted about this issue on the asp.net group didn't get
any replies so I'm trying to resolve my issue in the 'back end', so to
speak, by changing the data on the insert and select statements instead

Hugo,

Thanks for the pointers for SQL 2005 - I really should have inquired as
to which version the OP was using.

As for the SUBSTRING function - I did look at that but it was my last
resort!! It truly is a horrible way of doing things so I thought I'd
skip over it at this point.
I hope, for the OP sake, that it doesn't come down to using it! :-)

Best wishes for the New Year

Barry

[quoted text, click to view]

Barry, I get this error when I run that statement:

Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.

The post column is of data type text. Can we not search though and do a
replace on a column of that type?

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Jim,

Besides converting to varchar(8000) as suggested by Barry, here are two
other alternatives:

- If on SQL Server 2005, change your text columns to varchar(MAX).

- If on SQL Server 2000, and the text can be more than 8000 characters,
your only option is to (yuck!!) loop through chunks of the text with the
SUBSTRING function. Since the replacement text is longer than the
original, use chunks of slightly less than 8000 characters, so that the
results won't overflow.

--