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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.