Replacing the second carriage return

[asturt]

I have a string with multiple carriage returns in it. I need to remove
the second carriage return but leave all the rest. Anyone know of a
function that will do this?

I've come up with this:

/***********************************************/

DECLARE @MyString varchar(100)

SET @MyString='Line one.
Line two.
Line three.
Line four.'

SELECT @MyString

SELECT @MyString = LEFT(@MyString, CHARINDEX(CHAR(13), @MyString,
CHARINDEX(CHAR(13), @MyString, 0)+1)-1)
+ RIGHT(@MyString, LEN(@MyString)-(CHARINDEX(CHAR(13), @MyString,
CHARINDEX(CHAR(13), @MyString, 0)+1)+1))

SELECT @MyString

/***********************************************/

However, if there is a less convoluted way of doing this, I'd love to
know.

[Hugo Kornelis]

On 6 Oct 2006 12:46:15 -0700, as****@us.ci.org wrote:

>However, if there is a less convoluted way of doing this, I'd love to
know.

Hi asturt,

You can simplify it a bit using STUFF:

SELECT @MyString = STUFF(@MyString, CHARINDEX(CHAR(13), @MyString,
CHARINDEX(CHAR(13), @MyString, 0)+1), 2, '')

Note that the third (length) argument of the STUFF has to be 2, not 1,
because you have to replace both the CR and the LF.

--
Hugo Kornelis, SQL Server MVP