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] |
|
|
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))CheersMIK |
|
|
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 |
|
|
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? |
|
|
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? |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2013-04-30 : 13:08:48
|
Hi Lamprey,can you send me the sql please i'm cluelessthanks a lot |
|
|
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] |
|
|
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); |
|
|
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 :-) |
|
|
|