SQL Replace when some variable contains semicolons

Question

SQL Replace when some variable contains semicolons

Asked 7 years, 7 months ago

Viewed 1,048 times

1

I am using this command smoothly to replace class="texto1" for 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 semicolon ; receive this error message:

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

The command used in 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 mistake?

I searched online tools of "Character escape" but none modified the stretch with semicolon.

– Wesley

2017/04/11 at 23:06
Please specify your question, old text and text1?

– Roger Oliveira

2017/04/11 at 23:15
I would like to replace 'class="texto1" style="width:20px;margin-top:5px' with 'class="texto3' but I get the error shown.

– Wesley

2017/04/11 at 23:18
@Wesley: is it Mysql or SQL Server? The error message indicates Mysql but the sql-server tag is checked. Could you check?

– José Diz

2017/04/12 at 10:01
@Josédiz is an sql database running in Lynx by Locaweb, I am layman on the subject, I removed the sql-server tag, I would like to make the change through phpmyadmin

– Wesley

2017/04/12 at 16:56

2 answers

1

I managed using 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 on "Simulate search", but clicking on "Run" will work normally.

1

Wow, I’ve been racking my brain for two hours, and the answer was this, it makes a mistake just in "Simulate Research". Pretty inconsistency, huh? How to actually believe that the simulation will be equal to the execution?

– Henrique Barcelos

2020/10/17 at 04:05

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.

by Roger Oliveira • **682** points · Answer 1 · 2017-04-11T23:44:40+00:00

How are you calling this SQL?

If it’s 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 the sp so: exec substituitexto 'texto1','texto2','texto3'