ansaurus

Question

Answer 1

+11 A:

REPLACE(@string, CHAR(13)+CHAR(10), '')

Mitch Wheat 2009-06-04 16:13:25

Is chr working? isn't it char?

Peter 2009-06-04 16:16:57

ah, sorry typo!

Mitch Wheat 2009-06-05 01:56:05

Answer 2

+1 A:

Did you consider the REPLACE function?

John Saunders 2009-06-04 16:13:57

sure, i got stuck on char(13) forgetting the CR at first, then asked the question because i thought it should be in SO

Peter 2009-06-04 16:22:07

Answer 3

+6 A:

The Newline in T-SQL is represented by CHAR(13) & CHAR(10) (Carriage return + Line Feed). Accordingly, you can create a REPLACE statement with the text you want to replace the newline with.

REPLACE(MyField, CHAR(13) + CHAR(10), 'something else')

Cerebrus 2009-06-04 16:17:14

+ 1, i accepted Mitch Wheat cause he came up with it first (event though the chr function doesnt exist in my tsql)

Peter 2009-06-04 16:23:51

Hey, no problem! Mitch rocks as usual. Few guys can be as fast as him. ;-)

Cerebrus 2009-06-04 16:31:32

@Cerebrus: not sure that's true! ;) (voted you up as yours was correct)

Mitch Wheat 2009-06-05 01:57:43

Answer 4

+2 A:

Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(11), '')

RBarryYoung 2009-06-04 16:41:04

ansaurus

tags:

views:

answers:

Replace a newline in TSQL

related questions