SQL Replace when any variable contains a semicolon

Question

SQL Replace when any variable contains a semicolon

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I am using this command without problems to replace class="texto1" with class="texto2" :

UPDATE tabela
SET estilo = REPLACE (estilo,'class="texto1"','class="texto2"')
WHERE estilo LIKE 'class="texto1"';

It works perfectly, but when I want to replace class="texto1" style="width:20px;margin-top:5px that has a semicolon ; I get this error message:

# 1064 - You have an error in your SQL syntax; check the manual that correspond to your MySQL server version for the right syntax to use near 'FROM' table 'WHERE 1' at line 1

The command used on the second attempt was this:

UPDATE tabela
SET estilo =
REPLACE (estilo,
         'class="texto1" style="width:20px;margin-top:5px"',
         'class="texto3"')
WHERE estilo LIKE '%class="texto1" style="width:20px;margin-top:5px"%';

What could be causing the error?

mysql sql

asked by anonymous 12.04.2017 / 01:05

2 answers

0

How are you calling this SQL?

If it is in C # you can mount the string like this:

var sql = string.Format(@"UPDATE tabela SET estilo = REPLACE (estilo, '{0}', '{1}') where estilo LIKE '%class="{2}';", valor1, valor2, valor3);

You can also use a stored procedure:

CREATE PROCEDURE [dbo].[SubstituiTexto]
    @antigo varchar(50),
    @novo varchar(50),
    @valor varchar(50)
AS
BEGIN
   UPDATE tabela SET estilo = REPLACE (estilo, @antigo, @novo) where estilo LIKE @valor
END

and calls sp like this: exec substituitexto 'texto1','texto2','texto3'

12.04.2017 / 01:44

How to get back to the top of an Activity? Date Value 0000-00-00 can not be used in java Date

score 1 · Accepted Answer

I was able to use the command below:

UPDATE 'tabela'
SET 'estilo' = 'class="text3"'
WHERE 'estilo' LIKE 'class="text3" style="width:120px;text-align:center;"'

The error only appears if you click "Simulate Search", but clicking "Run" will work normally.