Replace single letter in mysql

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

Answers


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?


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()


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

Need Your Help

How can I use a JTable to display dynamic a list of data

java swing jtable

I'm quite new in Java, so please don't be so hard if this question is very stupid.