April 12, 2007 at 7:53 am
I am trying to replace a trademark symbol (™) in a varchar column with an empty string (''). I can't figure out how to identify this symbol in the column for selection and replacement purposes. When I look at this character outside of SQL Server, it equates to ASCII 153. When I inspect the character in SQL Server using the ASCII function, it equates to ASCII 84 ('T').
I've tried everything that I know how to do to replace this character, but to no avail. I'm sure it's just my lack of experience. Can anyone please help?
Thanks.
April 12, 2007 at 8:56 am
Joel,
not sure what you have tried, but this works on my system:
SELECT REPLACE('Microsoft™',char(153),'')
Markus
[font="Verdana"]Markus Bohse[/font]
April 12, 2007 at 10:56 am
It must be how the database or table is defined. For some reason, if I run this on my system, it returns "MicrosoftT" from the selection. The "T" in this string is ASCII 84. I'm not sure what is going on.
April 12, 2007 at 11:02 am
Maybe the text source was a word document and that the text was formatted that way... or any other variations on the same theory.
What is the ascii code of the M next to the T?
If you can't replace using the "correct" ascii code then you'll have to find all strings containing "tm" and manually checking them one at the time to validate wether or not you have to replace them... or skip the problem altogether if it's not worth the time.
April 12, 2007 at 11:06 am
There is no "M" in the string. The (TM) symbol is one character Hex 99.
April 12, 2007 at 11:09 am
Are you sure you are getting the ascii code for the right T then?? I don't see any other valid explaination.
April 12, 2007 at 11:29 am
I know that if I run this:
SELECT REPLACE('Microsoft™',char(153),'')
The result in hex is: '4D6963726F736F667454'.
The replacement is not occurring because the ™ symbol is being translated to a 'T' (Hex 54) instead of Hex 99. When data is stored in a varchar column within the database, it is visible as a ™ symbol. I don't quite understand it myself because if I select from it, it will return the Hex 99 symbol. If I use functions or reference it as ASCII, it converts it to 'T'.
I know I'm probably not making much sense here, but thought I would see if anyone has seen something like this before.
April 12, 2007 at 1:16 pm
SELECT REPLACE('Microsoft™',char(153),'')
works fine for me.
May I ask why you are bothering with the HEX translation??
April 12, 2007 at 1:21 pm
I'm not physically translating to/from hex. I am trying to show the hexadecimal equivalent of the result from the ASCII('™') function during operations. For some reason the ™ on your SQL server is fine and is being interpretted by your server as hex 99/dec 153. On mine it is being interpretted as hex 54/dec 84.
April 12, 2007 at 1:28 pm
Can you post the code you are using to arrive at those hex values so we both talk the same langage.
April 13, 2007 at 8:29 am
Instead of me posting the various SQL that I have used to debug, let me just show you everything that I am experiencing.
I have a SQL Server database (SBO_Base) that was provided to us by a large enterprise software company. It has a table called OITM with many columns. The OITM table stores item information (i.e. items that are being sold on sales orders). For the sake of this message, I'm going to limit it to two columns: ItemCode and ItemName. Here is the test data for those two columns:
ItemCode = 'A2-IC-6', ItemName = 'Episode™ A2-Series In-Ceiling Speakers (Pair) (Formerly A2-IC)'
ItemCode = 'B10001-1', ItemName = 'Small Mount Bracket -White'
First off:
1. If I run SELECT REPLACE('Microsoft™',char(153),'') on the master database, it works fine.
2. If I run SELECT REPLACE('Microsoft™',char(153),'') on the SBO_Base database, I get a response of MicrosoftT. This tells me that there is some setting on the database that is off.
3. If I run SELECT * FROM OITM WHERE ItemName LIKE '%™%', both rows in my test scenario are returned.
4. If I run UPDATE OITM SET ItemName = REPLACE(ItemName, char(153), '') Nothing gets updated.
Does this help?
By the way, I appreciate you taking some time out to help.
Thanks.
April 13, 2007 at 2:25 pm
What is the collation of the server / database / column in the table?
April 13, 2007 at 2:30 pm
SQL_Latin1_General_CP850_CI_AS for all.
April 13, 2007 at 2:34 pm
What if you backup / restore the rogue db to another server or even to the same server under a different name.
May 3, 2016 at 2:27 pm
Hi,
You must be set to code page 850 to get the Microsoft T - works fine on code page 1252
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply