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 2008 Forums
 Transact-SQL (2008)
 simple replace

Author  Topic 

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-29 : 12:30:43
Hi All,

I got a table that has tons of <br>
What statement do i need to use to replace them with Line breaks ?

thanks a lot

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 12:49:05
[code]UPDATE Tbl SET YourColumnName = REPLACE(YourColumnName,'<br>',CHAR(13)+CHAR(10));[/code]
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-29 : 12:49:20
Check if this works for you ..

Replace (ColumnName,'<br>',char(13))

Cheers
MIK
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 17:24:25
To add to what MIK and I posted: Depending on the OS for which you are targeting the resultant data, you would want to replace with a LF (char(10)), a CR only (char(13)) or both as CR+LF or LF+CR. Windows uses CR+LF. http://en.wikipedia.org/wiki/Newline
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-30 : 12:30:52
aaah forgot to saw it is a TEXT field.
This doesn't work.
How should i do it?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-30 : 12:34:10
I haven't tried this, but maybe try casting the TEXT to (N)VARCAHR(MAX), do the repalcement and then cast it back to TEXT?
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-30 : 13:08:48
Hi Lamprey,

can you send me the sql please i'm clueless

thanks a lot
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-30 : 13:54:51
[code]UPDATE Tbl SET YourColumnName = REPLACE(CAST(YourColumnName AS VARCHAR(MAX)),'<br>',CHAR(13)+CHAR(10));
[/code]
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-30 : 14:42:33
quote:
Originally posted by James K

UPDATE Tbl SET YourColumnName = REPLACE(CAST(YourColumnName AS VARCHAR(MAX)),'<br>',CHAR(13)+CHAR(10));


Again, I haven't tried, but you might need to cast it back to TEXT before the update:

UPDATE Tbl SET YourColumnName = CAST(REPLACE(CAST(YourColumnName AS VARCHAR(MAX)),'<br>',CHAR(13)+CHAR(10)) AS TEXT);
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-30 : 15:36:44
Thanks a lot that did the trick!
So now you know it works :-)
Go to Top of Page
   

- Advertisement -