Removing Unknown Character in SQL Server DB Field

try having the computer tell you what it is seeing by using the ASC() function to tell you what the ASCII number of the individual characters making up the entry you want to get rid of.  This will tell you what character(s) you need to replace.  Chances are, it ain't a space.

Once the ASCII code of the specific character is identified, you may use the following threads previously discussed in ExpertsExchange:

• https://www.experts-exchange.com/questions/29193487/Sql-query-replacing-special-character.html
• https://www.experts-exchange.com/questions/29205689/How-do-I-find-replace-all-special-characters-in-a-sql-server-table-columns-using-a-sql-script.html

You may test this in SSMS and it could give you an idea how to handle the unwanted character:

DECLARE @tst nvarchar(10) = N'╔ẊṼ∑╗'  -- possibly not displayed correctly at EE
DECLARE @vb varbinary(20) = 0x54258A1E7C1E11225725  -- varbinary representation of the above Unicode string
DECLARE @vb2 varbinary(20) = 0x54258A1E7C1E11225725FFF1 
SELECT @tst, @vb, CAST(@tst AS varbinary), CAST(@vb AS nvarchar), CAST(@vb2 AS nvarchar)

Select allOpen in new window

Update: EE knows how to display Unicode. This was not true in the past...  +1 for EE!

BTW, what means the "x" in your Unicode point U+FFFx ?  Characters from U+FFF0 to U+FFFF are used to indicate problems when a system is unable to determine correct Unicode point during some conversion etc. U+FFFD is probably your case. More info: https://en.wikipedia.org/wiki/Specials_(Unicode_block)

You should also investigate the source of your Unicode texts.

Nakul's links should give you an idea how to remove the character

Looks like it's ASCII is 160...a non-breaking space.  Could that have come from a Mac?  What is the best way to replace every instance of that using classic asp?  I am pretty much a novice at sql server commands...pretty much just stick with SSMS and some simple queries.

Non-breaking space is widely used character which is possible to enter at any keyboard in almost all languages (key combination when everything fails is ALT+0160). It surely does not have Unicode point U+FFFx.
Before any removal attempt happens you should show the database column data type and the column text converted to varbinary.

You may identify table rows containing this character in given column by the following query (valid for US English and char/varchar data type):

SELECT * FROM YourTable WHERE CHARINDEX(CHAR(160), YourColumn) > 0 

Select allOpen in new window

Thank you.  Looks like I have about 230 instances of that.  What is the best way to remove them?

It worked beautifully!  Thanks!!