Replacing CRLF's with <BR> in a view

Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column
)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:

> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>