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?
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 - update myFile set myNotesField = replace(cntnotes, "ì", " "
All is well now.
23 9304
0xAC0D is how you would represent the string in MySQL.
Are you saying the select statement would look like this - 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?
Is this the correct way to write it? - 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.
- UPDATE table
-
SET field = REPLACE(field, 0xAC0D, 0x0D0A)
Your answer gave me this error
"Unknown coulumn '0x0D0A' in field list"
So I am still stuck.
Works fine for me.
What query did you use? What version of MySQL are you using?
Ok I got the code to run without errors, but no update occurred either.
I am using version 5.0
this is my command -
update myTable set myField = replace(myField, 0xAC0A, 0x0D0A)
-
I also ran this code -
update myTable set myField = replace(myField, 0xAC0A, 0x0A)
-
then I ran it with a where clause to test just one specific record. That did not work either. -
update myTable set myField = replace(myField, 0xAC0A, 0x0A) where myKey = myKeyValue
-
the character that is creating the problem is "AC". The character combination is "AC0A"
How do you know that the character is hexadecimal 0xAC?
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"
Please attach the images to the thread itself. Image sharing websites are blocked by our firewall.
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.
Try png. And make sure the file size and resolution is below the limits.
Ok this was the best I could do. I inserted the screen shots in a word document. that seemed to take,
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.
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.
Just remember to try both combinations in case the original replace changed one of the characters.
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 -
UPDATE MYFILE SET NOTESFIELD = REPLACE(NOTESFIELD, 0xC3AC, 0x0D) WHERE MYKEY = 'KEYVALUE'
-
Inserting a blank space -
UPDATE MYFILE SET NOTESFIELD = REPLACE(NOTESFIELD, 0xC3AC, 0x20) WHERE MYKEY = 'KEYVALUE'
-
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?
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 -
insert into text set f1 = 1, f2 = "large ì Cantera"
-
I then ran the update command -
UPDATE test SET f2 = REPLACE(f2, 0x0C3AC, 0x20)
-
Nothing changed.
I then just tested replace "large" with "small" like this -
UPDATE test SET f2 = REPLACE(f2, "large", "small")
-
and that worked fine the record was updated. So then I tested this code and it finally worked -
UPDATE test SET f2 = REPLACE(f2, "ì", " ")
-
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.
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.
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 - update myFile set myNotesField = replace(cntnotes, "ì", " "
All is well now.
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jane Doe |
last post by:
Hi,
I need to search and replace patterns in web pages, but I
can't find a way even after reading the ad hoc chapter in New Rider's
"Inside JavaScript".
Here's what I want to do:
function...
|
by: DaveJG |
last post by:
I have a couple of questions involving the search/replace in the IDE
and regular expressions. Any help would be greatly appreciated.
1. How would I replace two line feeds with one? That is
...
|
by: SeaPlusPlus |
last post by:
I want to convert large files of prose to xhtml and so I need a way to
remove unwanted line wraps. So, I'm looking for a freebee editor that
has the capability of searching for a single "carriage...
|
by: GlennH |
last post by:
I am having trouble removing a pipe character using Regex.Replace -
see the 2 NUnit tests below:
The first replace works fine and the second Replace does not work.
I've tried escaping the pipe...
|
by: Michael Peters |
last post by:
is there a way to replace a certain sequence of characters by line feed
(vbCrLf ), for all text columns in a table, using Search+Replace?
-Michael
|
by: Kay |
last post by:
Hi All,
I'm trying to write a small sub/function to replace some (invalid) character
in a combo box, or may be even text box. For example, I have several combo
boxes that allows user to select...
|
by: Martin Kulas |
last post by:
Hello!
How do I replace a single character in a string at a given position?
From programming languages like C I expect something like that:
Traceback (most recent call last):
File...
|
by: vvenk |
last post by:
Hello:
I have a string, "Testing_!@#$%^&*()". It may have single and double
quotations as well.
I would like to strip all chararcters others than a-z, A-Z, 0-9 and the comma.
I came across...
|
by: davidson1 |
last post by:
hai friends..
I have the following code which should the user to enter only alphabetic characters...but this program...allow user to enter { } |\`~ characters also..how to avoid that.........
...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |