Using REPLACE with NCHAR in a stored procedure

[Lauren Quantrell]

I'm using replace in a stored procedure to eliminate carriage returns
a user might have entered in an Access field.
The problem is that even though it seems to strip out the carriage
return I can't get rid of the "□" character.

This is my code:

DECLARE @myReturn nvarchar(1)
SELECT @myReturn = NCHAR(10)

REPLACE(UserAddress,@myReturn,' ') AS myTEXT

For example, the user has entered:

123 Oak Street
Suite 101
What I get after running the replace is: 123 Oak Street □ Suite
101

lq

[Erland Sommarskog]

Lauren Quantrell (la*************@hotmail.com) writes:

I'm using replace in a stored procedure to eliminate carriage returns
a user might have entered in an Access field.
The problem is that even though it seems to strip out the carriage
return I can't get rid of the "□" character.

This is my code:

DECLARE @myReturn nvarchar(1)
SELECT @myReturn = NCHAR(10)

REPLACE(UserAddress,@myReturn,' ') AS myTEXT

For example, the user has entered:

123 Oak Street
Suite 101
What I get after running the replace is: 123 Oak Street □ Suite
101

Not really sure what the problem is, but to get rid of CR, pass nchar(13)
as the second argument to replace(), and to get rid of □ pass
nchar(9633):

declare @x nvarchar(200),
@y nvarchar(200)
select @x = 'This is text with char 9633 (' + nchar(9633) +
') and a CR (' + nchar(13) + ')in it.'
select @y = replace(@x, nchar(13), '/')
select @y = replace(@y, nchar(9633), '/')
select @x
select @y
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Lauren Quantrell]

Erland,
Thanks for your response. If I remove only NCHAR(10) or only NCHAR(13)
I still get the character &#9633 (which is a small square box.) But
thanks to your pointing out NCHAR(13) I am now using the following and
it works to remove both the return and the square box character:

REPLACE(REPLACE(UserAddress,NCHAR(13),''),NCHAR(10 ),'')

Thanks!
lq
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

Lauren Quantrell (la*************@hotmail.com) writes:

I'm using replace in a stored procedure to eliminate carriage returns
a user might have entered in an Access field.
The problem is that even though it seems to strip out the carriage
return I can't get rid of the "□" character.

This is my code:

DECLARE @myReturn nvarchar(1)
SELECT @myReturn = NCHAR(10)

REPLACE(UserAddress,@myReturn,' ') AS myTEXT

For example, the user has entered:

123 Oak Street
Suite 101
What I get after running the replace is: 123 Oak Street □ Suite
101

Not really sure what the problem is, but to get rid of CR, pass nchar(13)
as the second argument to replace(), and to get rid of □ pass
nchar(9633):

declare @x nvarchar(200),
@y nvarchar(200)
select @x = 'This is text with char 9633 (' + nchar(9633) +
') and a CR (' + nchar(13) + ')in it.'
select @y = replace(@x, nchar(13), '/')
select @y = replace(@y, nchar(9633), '/')
select @x
select @y