|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Tring to replace a Line Break with a <br>I am trying to replace in a SELECT statement a field's value that has Line
Breaks and replace each Line Break with a <br> I cannot fined what the value should be to represend a Line Break, I tried CRLF but it does not recognize it. I know in VB its vbcrlf. SELECT REPLACE(MyField, CRLF, '<br>') FROM MyTable Thank you. CRLF is often char(10) + char(13)
So, SELECT replace( MyField, ( char(10) + char(13) ), '<br>' ) FROM MyTable -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Marco Napoli" <marco@avantitecnospam.com> wrote in message news:e2lVFhAnGHA.620@TK2MSFTNGP05.phx.gbl... >I am trying to replace in a SELECT statement a field's value that has Line > Breaks and replace each Line Break with a <br> > > I cannot fined what the value should be to represend a Line Break, I tried > CRLF but it does not recognize it. I know in VB its vbcrlf. > > > SELECT REPLACE(MyField, CRLF, '<br>') > FROM MyTable > > > Thank you. > > -- > > Peace in Christ > Marco Napoli > http://www.ourlovingmother.org > > Thank you so much.
Marco "Arnie Rowland" <ar***@1568.com> wrote in message news:eVrOpoAnGHA.5056@TK2MSFTNGP04.phx.gbl... CRLF is often char(10) + char(13)So, SELECT replace( MyField, ( char(10) + char(13) ), '<br>' ) FROM MyTable -- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam Show quote "Marco Napoli" <marco@avantitecnospam.com> wrote in message news:e2lVFhAnGHA.620@TK2MSFTNGP05.phx.gbl... >I am trying to replace in a SELECT statement a field's value that has Line > Breaks and replace each Line Break with a <br> > > I cannot fined what the value should be to represend a Line Break, I tried > CRLF but it does not recognize it. I know in VB its vbcrlf. > > > SELECT REPLACE(MyField, CRLF, '<br>') > FROM MyTable > > > Thank you. > > -- > > Peace in Christ > Marco Napoli > http://www.ourlovingmother.org > > > CRLF is often char(10) + char(13) Actually, a proper CR/LF pair is going to be CHAR(13)+CHAR(10), but of course that depends on the source of the data in the first place. If you define a pair like that in the -- admittedly, more logical -- order you've presented, the line break won't always be maintained correctly (it depends on how the text gets translated on select, in transport, and at the destination). In Query Analyzer (with results to text, because results to grid won't be very useful), observe the difference: SELECT 'foo' + CHAR(10) + CHAR(13) + 'bar'; SELECT 'foo' + CHAR(13) + CHAR(10) + 'bar'; There is a big difference for me on 8.00.760, 8.00.2187, 9.00.1399.06 and 9.00.2153.00. Often, of course, you will find that text from Unix or Mac or Java will be missing the CHAR(13) and only have the CHAR(10). This can also happen with lazy programming where people find they can get away with CHAR(10) for certain output formats (e.g. pasting results into Notepad) without realizing that the CHAR(13) was actually important too. For HTML explicitly, it wouldn't hurt to do it this way, if you can't be sure of the way your data will look: REPLACE(REPLACE(col, CHAR(13), '<br>'), CHAR(10), '<br>') This is because when there are successive <br><br> with nothing in between, all but the first are ignored (at least the last time I checked in IE, FireFox and Safari; who knows how long that will stay true). So, you have a <br> wherever you had just a CHAR(13) or just a CHAR(10), and -- at least in the short term -- you have an inconsequential extra <br> where you previously had a valid CR/LF pair or an invalid LF/CR pair. A Excellent clarifications Aaron. And thanks for catching my reversal -wrote
it quickly without much thinking. Just in case there is every a browser change that recognizes two sequential <br>, one could wrap your final suggestion with one more replace -replacing <br><br> with a single <br>. If it is needed regularly, putting it in a function. -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:ON1jsFBnGHA.4224@TK2MSFTNGP03.phx.gbl... >> CRLF is often char(10) + char(13) > > Actually, a proper CR/LF pair is going to be CHAR(13)+CHAR(10), but of > course that depends on the source of the data in the first place. > > If you define a pair like that in the -- admittedly, more logical -- order > you've presented, the line break won't always be maintained correctly (it > depends on how the text gets translated on select, in transport, and at > the destination). In Query Analyzer (with results to text, because > results to grid won't be very useful), observe the difference: > > SELECT 'foo' + CHAR(10) + CHAR(13) + 'bar'; > > SELECT 'foo' + CHAR(13) + CHAR(10) + 'bar'; > > There is a big difference for me on 8.00.760, 8.00.2187, 9.00.1399.06 and > 9.00.2153.00. > > Often, of course, you will find that text from Unix or Mac or Java will be > missing the CHAR(13) and only have the CHAR(10). This can also happen > with lazy programming where people find they can get away with CHAR(10) > for certain output formats (e.g. pasting results into Notepad) without > realizing that the CHAR(13) was actually important too. > > For HTML explicitly, it wouldn't hurt to do it this way, if you can't be > sure of the way your data will look: > > REPLACE(REPLACE(col, CHAR(13), '<br>'), CHAR(10), '<br>') > > This is because when there are successive <br><br> with nothing in > between, all but the first are ignored (at least the last time I checked > in IE, FireFox and Safari; who knows how long that will stay true). So, > you have a <br> wherever you had just a CHAR(13) or just a CHAR(10), > and -- at least in the short term -- you have an inconsequential extra > <br> where you previously had a valid CR/LF pair or an invalid LF/CR pair. > > A > Thank you, that was a great point.
Marco Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:ON1jsFBnGHA.4224@TK2MSFTNGP03.phx.gbl... >> CRLF is often char(10) + char(13) > > Actually, a proper CR/LF pair is going to be CHAR(13)+CHAR(10), but of > course that depends on the source of the data in the first place. > > If you define a pair like that in the -- admittedly, more logical -- order > you've presented, the line break won't always be maintained correctly (it > depends on how the text gets translated on select, in transport, and at > the destination). In Query Analyzer (with results to text, because > results to grid won't be very useful), observe the difference: > > SELECT 'foo' + CHAR(10) + CHAR(13) + 'bar'; > > SELECT 'foo' + CHAR(13) + CHAR(10) + 'bar'; > > There is a big difference for me on 8.00.760, 8.00.2187, 9.00.1399.06 and > 9.00.2153.00. > > Often, of course, you will find that text from Unix or Mac or Java will be > missing the CHAR(13) and only have the CHAR(10). This can also happen > with lazy programming where people find they can get away with CHAR(10) > for certain output formats (e.g. pasting results into Notepad) without > realizing that the CHAR(13) was actually important too. > > For HTML explicitly, it wouldn't hurt to do it this way, if you can't be > sure of the way your data will look: > > REPLACE(REPLACE(col, CHAR(13), '<br>'), CHAR(10), '<br>') > > This is because when there are successive <br><br> with nothing in > between, all but the first are ignored (at least the last time I checked > in IE, FireFox and Safari; who knows how long that will stay true). So, > you have a <br> wherever you had just a CHAR(13) or just a CHAR(10), > and -- at least in the short term -- you have an inconsequential extra > <br> where you previously had a valid CR/LF pair or an invalid LF/CR pair. > > A >  |
|||||||||||||||||||||||
Other interesting topics