469,935 Members | 2,219 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,935 developers. It's quick & easy.

Search and replace unwanted character

Claus Mygind
571 512MB
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.

23 8917
Rabbit
12,516 Expert Mod 8TB
0xAC0D is how you would represent the string in MySQL.
Apr 20 '13 #2
Claus Mygind
571 512MB
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
571 512MB
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
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. UPDATE table
  2. SET field = REPLACE(field, 0xAC0D, 0x0D0A)
Apr 20 '13 #5
Claus Mygind
571 512MB
Your answer gave me this error

"Unknown coulumn '0x0D0A' in field list"

So I am still stuck.
Apr 29 '13 #6
Rabbit
12,516 Expert Mod 8TB
Works fine for me.

What query did you use? What version of MySQL are you using?
Apr 29 '13 #7
Claus Mygind
571 512MB
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
12,516 Expert Mod 8TB
How do you know that the character is hexadecimal 0xAC?
May 6 '13 #9
Claus Mygind
571 512MB
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
12,516 Expert Mod 8TB
Please attach the images to the thread itself. Image sharing websites are blocked by our firewall.
May 13 '13 #11
Claus Mygind
571 512MB
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
12,516 Expert Mod 8TB
Try png. And make sure the file size and resolution is below the limits.
May 13 '13 #13
Claus Mygind
571 512MB
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, 374 views)
May 13 '13 #14
Rabbit
12,516 Expert Mod 8TB
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
571 512MB
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
12,516 Expert Mod 8TB
Just remember to try both combinations in case the original replace changed one of the characters.
May 14 '13 #17
Claus Mygind
571 512MB
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
571 512MB
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
12,516 Expert Mod 8TB
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
571 512MB
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
571 512MB
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
12,516 Expert Mod 8TB
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
571 512MB
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.

Similar topics

4 posts views Thread by Jane Doe | last post: by
23 posts views Thread by SeaPlusPlus | last post: by
1 post views Thread by GlennH | last post: by
2 posts views Thread by Michael Peters | last post: by
5 posts views Thread by Martin Kulas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.