simple replace - SQL Server Forums

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]

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

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 clueless

thanks 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 :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources