How to use str_replace in single quotation marks without removing the ones that are needed?

In case your question is not oriented towards PHP, but MySQL.

But let's go:

Execute the query by replacing the name of your_table and your_column

UPDATE sua_tabela
SET sua_coluna = REPLACE(sua_coluna, "\'", '"')
WHERE sua_coluna LIKE "%\'%"

An alternate way is to select the records you want to correct.

SELECT 

campo,
 ROUND (   
        (
            LENGTH(campo)
            - LENGTH( REPLACE ( campo, '\'', '') ) 
        ) / LENGTH('\'')        
    ) AS count

FROM

'regexp'

HAVING (
ROUND (   
        (
            LENGTH(campo)
            - LENGTH( REPLACE ( campo, '\'', '') ) 
        ) / LENGTH('\'')        
    )
) % 2 = 1

In this example, the query will return all that have a single quote (') and the sum of their occurrences per row, and the number of occurrences must be odd.

What is the logic in doing this?

If odd numbers exist, there are probably occurrences like \ 'Gurizinha \' s \ '.

This would eliminate the need to look at a good part of the results. For all other results with resulting sum in pairs, just apply a general replace, changing the single quote escaped by a double quote without the escape character.

Still, it will not resolve 100%, but at least it will have a list of all rows ids that need to be fixed.

There are other ways with REGEXP, however, there is no way to use REGEXP with REPLACE. I would have to use a UDF. But I think you can solve it in a simpler way without needing UDFs.

Look for regular expressions with PHP, I'm not yet experienced with them, but I believe the example below solves your problem:

<?php
    function ModificarTXT($str)
    {
        // Pego toda palavra que começa com \' e substituo por "
        $string = ereg_replace("^([/\'])", '"',$str);

        //Pego toda palavra que termina com \' e substituo por "
        $string = ereg_replace("([/\'])$", '"',$string);
        return $string;
    }

    echo ModificarTXT("\'Gurizinha\'s\'"); // Teste da funcao
?>

The problem is with your connection to the bank.

Use PDO that your information will be stored exactly as it was, without having to remove or replace special characters, quotation marks, etc. And when you recover, it will be the same way you entered it. PDO also accepts parameterized statements (Preparement Statement) that will protect you from SQL injections.

To connect:

// Dados da conexão
$host = 'localhost';
$port = '3306';
$user = 'root';
$pass = '123456';
$dbname = 'meubanco'

// Configuração para garantir a codificação correta caso esteja usando utf8
$config = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8");

// Realiza a conexão
$pdo = new PDO("mysql:host=$host;port=$port;dbname=$dbname", $user, $pass, $config);

Parameterized INSERT Example:

// Recebe variáveis
$codigo = $_POST['codigo'];
$nome = $_POST['nome'];

// Define a query usando '?' no lugar das variáveis
$query = "INSERT INTO table1 (codigo, nome) VALUES (?,?)";

// Prepara
$stmt = $pdo->prepare($query);

// Seta os parametros definidos como '?' na query de acordo com a ordem
$stmt->bindValue(1, $codigo, PDO::PARAM_INT);
$stmt->bindValue(2, $nome, PDO::PARAM_STR);

// Executa query
$stmt->execute();

// Pega o total de linhas afetadas
$total = $stmt->rowCount();

// Verifica se inseriu
if( $total > 0 )
    echo "Inseriu!";
else
    echo "Não Inseriu!";

Parameterized SELECT Example:

// Recebe variáveis
$codigo_minimo = $_POST['codigo_minimo'];

// Define a query usando '?' no lugar das variáveis
$query = "SELECT * FROM table1 WHERE codigo > ?)";

// Prepara
$stmt = $pdo->prepare($query);

// Seta os parametros definidos como '?' na query de acordo com a ordem
$stmt->bindValue(1, $codigo_minimo, PDO::PARAM_INT);

// Executa query
$stmt->execute();

// Muda de acesso as colunas para o modo pelo nome
$stmt->setFetchMode(PDO::FETCH_ASSOC);

// Usando fetchAll você pega todos resultados em um array
$result = $stmt->fetchAll();

var_dump($result);

var_dump ($ result) would print something like this:

array (size=3)
   0 => array (size=2)
       'codigo' => 1
       'nome' => "bla bla bla"
   1 => array (size=2)
       'codigo' => 2
       'nome' => "ble ble ble"
   2 => array (size=2)
       'codigo' => 3
       'nome' => "blu blu blu"

To disconnect:

// basta setar como NULL
unset($pdo); 

For more information about using PDO , visit this link .

Use a regular expression to replace 'by' at the beginning and end of whole words:

preg_replace_callback('/(?<=\s|\t|\r|\n| )(\'.+\')(?=\s|\t|\r|\n|\.|,| )/muU', function($match) {
    return '"' . substr($match[0], 1, -1) . '"';
}, $x);

You can use this function (udf) to replace all records that are already in the database:

REGEXP_REPLACE(col, '/(?<=\s|\t|\r|\n| )\'(?=.*\'[\s\t\r\n\., ])/', '"');
REGEXP_REPLACE(col, '/(?<=[\s|\t|\r|\n| ]\'.*)\'(?=[\s\t\r\n\., ])/', '"');

Or create a script to select and update all columns:

$rows = $dbh->query("SELECT id, col FROM tabela")->fetchAll(\PDO::FETCH_ASSOC);
$sth = $dbh->prepare("UPDATE tabela SET col = ? WHERE id = ?");
foreach($rows as $row) {
    $str = preg_replace_callback('/(?<=\s|\t|\r|\n| )(\'.+\')(?=\s|\t|\r|\n|\.|,| )/muU', function($match) {
       return '"' . substr($match[0], 1, -1) . '"';
    }, $row['col']);
    if($str == $row['col']) continue;
    $sth->execute(array( $str, $row['id'] ));
    $sth->closeCursor();
}