Finding a certain character

Ballita

Hi all, I have different characters in a string, My string may contain CHAR(10) or CHAR(13) + CHAR(10). If my string contain only CHAR(10) then I want to replace it with CHAR(13) + CHAR(10). And if my string contain CHAR(13) + CHAR(10) then I don't want to replace it. Can any body give me a solution to this.

Thanks & Regards Mishra

Manas Bhardwaj

Find the characters in string like this...

DECLARE @position int, @string char(8)
-- Initialize the current position and the string variables.
SET @position = 1
SET @string = 'New Moon'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
GO

You may modify/build a new string upon your requirements...

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Syed Mehroz Alam

We can do this in two cascaded steps: 1. Replace all occurrences of Char(10) with Char(13)+Char(10) 2. Replace all occurrences of Char(13)+Char(13)+Char(10) with Char(13)+Char(10) Note that Step 1 replaces some unwanted occurrences also (i.e. Char(13)+Char(10) occurrences), but the second step fixes those unwanted wrong replaces. Have fun with this piece of code:

declare @myTestString varchar(50)

set @myTestString = char(10)+char(13)+char(10)+char(10)+char(13)+char(13)+char(13)+char(10)

set @myTestString = replace( @myTestString, char(10), char(13)+char(10) )
set @myTestString = replace( @myTestString, char(13)+char(13)+char(10), char(13)+char(10) )

Cheers, Syed Mehroz Alam

My Blog My Articles