Avatar of Bob Schneider
Bob Schneider
Flag for United States of America asked on

Removing Unknown Character in SQL Server DB Field

I am trying to replace a Unicode block character in my sql server db.  It shows up on my web site as U+FFFx (Question Mark inside of a rhombus) on my classic asp website but looks like a space in my db.  I have tried to Trim the fields and I have tried to replace the space, the symbol.  I would really like to get these out of there.

Any help would be much appreciated.

SQLMicrosoft SQL ServerASP

Avatar of undefined
Last Comment
Bob Schneider

8/22/2022 - Mon
Mark Edwards

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.

Nakul Vachhrajani

Pavel Celba

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)

Open in new window

Update: EE knows how to display Unicode. This was not true in the past...  +1 for EE!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Pavel Celba

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
Bob Schneider

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.
Pavel Celba

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 

Open in new window

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Bob Schneider

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

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Bob Schneider

It worked beautifully!  Thanks!!