adam kim adam kim - 1 year ago 65
SQL Question

replacing one character with another when not next to a certain other character

I know how to do this in code with loops and if statements, but im not sure how to do this in tsql.

Note, when I say CR and LF i mean char(13) and char(10)

basically i want to find everything with a CR and replace it with a CRLF.
Hoever, It is slightly more complicated than this because if I ever hit a CRLF, I dont want the result to be CRCRLF.
I want to replace all instances in a column, not just the first occurrence. Any idea on the tsql way of doing this would be great.

One horrible hack would be to go through the and replace any CR with CRLF resulting in some CRCRLF and then run a second update to replace any CRCRLF with CRLF but that can't be the best way could it?

Answer Source

Well, one method would be to replace the current instances of CRLF with CR and then do the replacement:

with chars as (
      select char(10) as lf, char(13) as cr
update t
    set col = replace(replace(col, cr + lf, cr), cr, cr + lf)
    from t cross join
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download