replace TM and R characters from SQL

I have a notes field in a sql table which has some TM and (R) symbols which are causing an issue with an XML export file.  I need a script to replace those values with a blank.

Any ideas how to do that??
red_75116Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajvjaCommented:
First of all,

  how are you exporting to xml?

If you are doing this in SSIS, replace those symbols with some text and convert it back before writing to xml.

The same should apply even you are doing from other way
0
red_75116Author Commented:
my application has a built in XML export which I cannot modify.  I manualy changed these values last week and the export worked, but now there are more.  I was hoping to find a way to search and replace them before the xml sync occurs.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
-- Replace copyright sign with a blank
UPDATE YourSQLTable
SET Notes = REPLACE(Notes, char(174), ' ')

-- Replace trademark sign with a blank
UPDATE YourSQLTable
SET Notes = REPLACE(Notes, CHAR(153), ' ')

-- Testing
declare @str varchar(10)
SELECT @str = 'abc' + CHAR(174) + 'def' + CHAR(153)

select @str = REPLACE(@str, char(174), ' ')
select @str
select @str = REPLACE(@str, char(153), ' ')
select @str
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

red_75116Author Commented:
jimhorn,

That looks like it updated the values, but I am still getting the error.  I think there might be a few more issues with other characters.  I know that the ellipse symbol had a problem and probably the copyright symbol.  Is there a website you know of that I can find these char values?
0
red_75116Author Commented:
jimjorn,

one other thing. I have this script which takes about 5 minutes to run on my database, but this gives me all the IDs where the notes field has the problem.  I think this are non -unicode values.

Can this script be modified to replace all the non-unicode values with blanks??
;With cteNumbers as 
( 
        Select ROW_NUMBER() Over(Order By c1.object_id) as N 
        From sys.system_columns c1, sys.system_columns c2 
) 
Select Distinct cmp_wwn 
From cicmpy t 
        Join cteNumbers n ON n <= Len(cmp_note) 
Where UNICODE(Substring(cmp_note, n.N, 1)) > 255 
        OR UNICODE(Substring(cmp_note, n.N, 1)) <> ASCII(Substring(cmp_note, n.N, 1))

Open in new window

0
Anthony PerkinsCommented:
>> I know that the ellipse symbol had a problem and probably the copyright symbol. <<
I don't believe there is an ASCII value for an ellipsis and the copyright symbol is CHAR(169).

But what you need to be doing is identifyiing all the ASCII values and Xml encoding them as opposed to replacing them with blanks.
0
Anthony PerkinsCommented:
>>I think this are non -unicode values.<<
What is the data type for cmp_note?  If it is varchar then there is no need for > 255 in the WHERE condition.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
red_75116Author Commented:
The source is text and the target is nvarchar(max).  I don't have the option to change the xml encoding.  I need to replace the values.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I missed the XML part of the question.

>Is there a website you know of that I can find these char values?
 I started here ... http://www.fileformat.info/info/unicode/char/search.htm
0
Anthony PerkinsCommented:
>>I don't have the option to change the xml encoding<<
I am not suggesting you change the Xml encoding, but rather replace the invalid characters with the equivalent Xml encoded value rather than space.  So for example instead of:
REPLACE(cmp_note, '>', ' ')
I would do:
REPLACE(cmp_note, '>', '&gt;')

In any case, since you are for whatever reason using the wrong data type and to make matters worse a data type that supports unicode, you have a much more complicated situation.  This quite trivial using something like .NET, have you considered that option.
0
red_75116Author Commented:
I have an ERP solution and a Web portal solution and they provided an XML sync utility.  I do not have any options to change the encoding, but was told to updat ehte sql values andremove the characters that were causing the issue.

So, I am looking for a way to remove the invalid characters so the sync will progress.
0
Anthony PerkinsCommented:
>>So, I am looking for a way to remove the invalid characters so the sync will progress. <<
Ok, fair enough.  Then you will have to identify all the invalid Xml characters.
0
ben9Commented:
Maybe you can simply do this in the management studio.

select replace(cmp_note,'©','') from table where cmp_note like '%©%'

update table set cmp_note = replace(cmp_note,'©','') where cmp_note like '%©%'

and the same for ™ , or ® symbol. Simply find the problem character using a data browser, and copy it into the editor screen.

update table set cmp_note = replace(cmp_note,'™','') where cmp_note like '%™%'
0
jogosCommented:
>>So, I am looking for a way to remove the invalid characters so the sync will progress. <<
Ok, fair enough.  Then you will have to identify all the invalid Xml characters.
And that will be an everlasting job
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.