By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Search and replace unwanted character

Claus Mygind
100+
P: 571
Using MySQL 5.0 on a xP platform

I have a longtext column in my table. Somehow a character has been inserted (or transformed) into some of the records where the carriage/line return strings should be. I should have in hex format 0D 0A. But instead what I have is AC 0A.

Is there a way to search for the AC and replace it with 0D?
Apr 19 '13 #1

✓ answered by Claus Mygind

Well I finally resolved the problem. Instead of trying to replace the hex representation C3 AC. I simply ran the replace command for visual representation of the character like this
Expand|Select|Wrap|Line Numbers
  1. update myFile set myNotesField = replace(cntnotes, "", " "
All is well now.

Share this Question
Share on Google+
23 Replies


Rabbit
Expert Mod 10K+
P: 12,366
0xAC0D is how you would represent the string in MySQL.
Apr 20 '13 #2

Claus Mygind
100+
P: 571
Are you saying the select statement would look like this

Expand|Select|Wrap|Line Numbers
  1. select * from <myTable> where <myColumn> like concat("%", 0xAC0D, "%"
No wait that is the select statement, I need a search and replace statement. Can you expand on your advice and give the line?
Apr 20 '13 #3

Claus Mygind
100+
P: 571
Is this the correct way to write it?

Expand|Select|Wrap|Line Numbers
  1. SELECT REPLACE(t1.<myColumn>, 0xAC0D, 0x0D0A);
Not sure where I plug in the table name with the "from"? Also the "where" is not included, so how does this scan all my records. I have thousands of records to do this on one record at the time would be very difficult.
Apr 20 '13 #4

Rabbit
Expert Mod 10K+
P: 12,366
Expand|Select|Wrap|Line Numbers
  1. UPDATE table
  2. SET field = REPLACE(field, 0xAC0D, 0x0D0A)
Apr 20 '13 #5

Claus Mygind
100+
P: 571
Your answer gave me this error

"Unknown coulumn '0x0D0A' in field list"

So I am still stuck.
Apr 29 '13 #6

Rabbit
Expert Mod 10K+
P: 12,366
Works fine for me.

What query did you use? What version of MySQL are you using?
Apr 29 '13 #7

Claus Mygind
100+
P: 571
Ok I got the code to run without errors, but no update occurred either.

I am using version 5.0

this is my command
Expand|Select|Wrap|Line Numbers
  1. update myTable set myField = replace(myField, 0xAC0A, 0x0D0A)
  2.  
I also ran this code
Expand|Select|Wrap|Line Numbers
  1. update myTable set myField = replace(myField, 0xAC0A, 0x0A)
  2.  
then I ran it with a where clause to test just one specific record. That did not work either.
Expand|Select|Wrap|Line Numbers
  1. update myTable set myField = replace(myField, 0xAC0A, 0x0A) where myKey = myKeyValue
  2.  
the character that is creating the problem is "AC". The character combination is "AC0A"
May 6 '13 #8

Rabbit
Expert Mod 10K+
P: 12,366
How do you know that the character is hexadecimal 0xAC?
May 6 '13 #9

Claus Mygind
100+
P: 571
By Looking at the data in a binary format.





In the visible data screen shot note the character after the words "large" and "will". Then find the same characters in the binary formatted data.

Although now looking at the data in more detail, the space after large is "20", so what I want to get rid of is "C3 AC", which precedes the "0A"
May 13 '13 #10

Rabbit
Expert Mod 10K+
P: 12,366
Please attach the images to the thread itself. Image sharing websites are blocked by our firewall.
May 13 '13 #11

Claus Mygind
100+
P: 571
Wow I tried that and I got an invalid file format. tried both .gif and .jpg

these are the messages I get when I try to upload.

Upload Errors
visibleData.gif:
This is not a valid image file.
Hex data format.gif:
This is not a valid image file

Not sure what to do now.
May 13 '13 #12

Rabbit
Expert Mod 10K+
P: 12,366
Try png. And make sure the file size and resolution is below the limits.
May 13 '13 #13

Claus Mygind
100+
P: 571
Ok this was the best I could do. I inserted the screen shots in a word document. that seemed to take,
Attached Files
File Type: doc Hex Data.doc (167.0 KB, 327 views)
May 13 '13 #14

Rabbit
Expert Mod 10K+
P: 12,366
Well, there's your problem. You didn't mention C3 before. You just need to include that in your replace.

Don't forget you might have already replaced the AC so you may want to check the binary again.
May 14 '13 #15

Claus Mygind
100+
P: 571
Well to tell you the truth. Nothing was replaced when I tried to remove the AC alone. I will give it another shot and try to remove the combination of the characters based on your example. Will report back.
May 14 '13 #16

Rabbit
Expert Mod 10K+
P: 12,366
Just remember to try both combinations in case the original replace changed one of the characters.
May 14 '13 #17

Claus Mygind
100+
P: 571
I have now tried it a couple of ways and nothing gets replaced.

The unwanted character is still there. It is represented with C3AC in hex format

This is the replace commands I have tried
Expand|Select|Wrap|Line Numbers
  1. UPDATE MYFILE SET NOTESFIELD = REPLACE(NOTESFIELD, 0xC3AC, 0x0D) WHERE MYKEY = 'KEYVALUE'
  2.  
Inserting a blank space
Expand|Select|Wrap|Line Numbers
  1. UPDATE MYFILE SET NOTESFIELD = REPLACE(NOTESFIELD, 0xC3AC, 0x20) WHERE MYKEY = 'KEYVALUE'
  2.  
here is the actual data between the === lines with the errant character included after the words "will" and "large"

===============================================
See also Amer26

*************************

06/25/96 - Mr. Sanchez is handling due diligence for the large
Cantera project. I have sent him a packet of info and will
follow up. JDF
===============================================


Can you insert this text into a longtext field and the remove the character to see if you can replace it with a space?
May 22 '13 #18

Claus Mygind
100+
P: 571
I have now created a new table "test" with just two fields "f1" and "f2"

Where f1 is the key field and f2 is the longtext field

I inserted just one record

Expand|Select|Wrap|Line Numbers
  1. insert into text set f1 = 1, f2 = "large  Cantera"
  2.  
I then ran the update command
Expand|Select|Wrap|Line Numbers
  1. UPDATE test SET f2 = REPLACE(f2, 0x0C3AC, 0x20)
  2.  
Nothing changed.

I then just tested replace "large" with "small" like this
Expand|Select|Wrap|Line Numbers
  1. UPDATE test SET f2 = REPLACE(f2, "large", "small")
  2.  
and that worked fine the record was updated. So then I tested this code and it finally worked
Expand|Select|Wrap|Line Numbers
  1. UPDATE test SET f2 = REPLACE(f2, "", " ")
  2.  
May 22 '13 #19

Rabbit
Expert Mod 10K+
P: 12,366
Firstly, this is wrong 0x0C3AC because there are 6 characters there and hex representations of characters sets are usually 2 character combinations. You have an extra character in there.

Second, I'm not sure where and how you got your hex codes but when I pasted the string into a hex editor, I didn't see C3AC at all. What I saw was E30D.
May 22 '13 #20

Claus Mygind
100+
P: 571
You are correct about the leading 0. That was a copy and paste error.

Second. And did you note that when you changed from normal view to hex view that the changed to a . I suspect your hex viewer cannot represent the C3AC.

If I knew where the characters came from I would not have a problem. But they are there.

I am using the older MySQL Query Browser and when I display the record in the browser. Then click on my notes ( LONGTEXT )field, the field viewer let's me choose either "Text" or "Binary". When I select the "Binary" view, that is when the C3 AC appears.
Jun 3 '13 #21

Claus Mygind
100+
P: 571
Well I finally resolved the problem. Instead of trying to replace the hex representation C3 AC. I simply ran the replace command for visual representation of the character like this
Expand|Select|Wrap|Line Numbers
  1. update myFile set myNotesField = replace(cntnotes, "", " "
All is well now.
Jun 3 '13 #22

Rabbit
Expert Mod 10K+
P: 12,366
Glad you got it fixed.

And did you note that when you changed from normal view to hex view that the changed to a . I suspect your hex viewer cannot represent the C3AC.
No, it did not change. It wouldn't matter even if it did change the visual representation. Just because the character set may not have a visual representation of C3AC doesn't mean that my hex editor is going to change it to E30D. That's not how character sets and binary works. If the binary was C3AC, then my hex editor should have shown C3AC as well. Even if the character set I used didn't have a representation for it.
Jun 3 '13 #23

Claus Mygind
100+
P: 571
Well the reason I asked is, I got the same exact response as you (E30D) when I copied that character string into my own Hex viewer. While I can see the "" in the normal text mode.

I refer you back to my earlier post where I attached the photo to the thread. In it you can clearly see C3 AC representing the character.
Jun 3 '13 #24

Post your reply

Sign in to post your reply or Sign up for a free account.