0

How I can replace a single letter in any collumn / any row in selected mysql table? I found a solution but it works only with whole words. I has a problem with correct encoding, now I must fix those records. Replace in PHP will not work, it's over 50k records.

For example:

�wierk -> Świerk

or

Wizyt�wka -> wizytówka

Sankarann
  • 2,443
  • 4
  • 19
  • 55
user3253314
  • 3
  • 1
  • Take a look at [this](http://stackoverflow.com/questions/5956993/mysql-string-replace) – Chakradhar Vyza Jan 30 '14 at 13:28
  • 1
    What solution did you come up with only works with whole words? – AgRizzo Jan 30 '14 at 13:32
  • Mysql replace, any regexp wont work with this function. I can replace only whole words. It's hard to select letter that is marked as --> �. I don't know is is "ą", "ę" or other letter. You know what I mean? – user3253314 Jan 30 '14 at 13:37

3 Answers3

1

You can effectively replace a certain character this way :

update <table> set <field> = replace(<field>, CHAR(<old ascii>), CHAR(<new ascii>))

It works also with special characters like VT vertical tab, ascii 21 and so on.

To get the ASCII value you can use ord() 

$ascii = ord($char);

But first of all, I would ensure that it actually is wrong data you have in your table, and not just wrong encoding you get when you query.

have you tried mysql_set_charset('utf8') before you run your query?

davidkonrad
  • 77,311
  • 15
  • 189
  • 243
0

well you can iterate through each letter of the string if its a string 

$myString = "hello";

$length = strlen($myString);

for($a = 0; $a<=$length-1; $a++){
    echo $myString[$a]."<br>";
    if($mystring[$a] == '�'){
      /* do something  */
    }
}

there are other ways too like explode()

rockStar
  • 1,241
  • 11
  • 21
0

use LOCATE() to locate letter and SUBSTRING() and CONCAT() to fix it.

SELECT Content,CONCAT(SUBSTRING(Content,1,LOCATE('�', Content)-1),"Ś",SUBSTRING(Content,LOCATE('�', Content)+1)) FROM Table
Volkan Ulukut
  • 4,113
  • 1
  • 17
  • 35