473,320 Members | 1,881 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 9304
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, 426 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

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

Similar topics

4
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...
0
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 ...
23
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...
1
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...
2
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
5
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...
5
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...
4
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...
1
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......... ...
0
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.