Any mySQL/MariaDB gurus around? Need help with query

whu606

I'm looking to update from using Emotify plugin to using Emoji Extender.

I've got the plugin side working fine.

For existing posts, I need to replace, for example, ;lol with :lol:

However, I keep running into an issue that I assume relates to the semicolon.

A test query using this syntax:

UPDATE `gdn_comment` SET `Body`= REPLACE (`Body`, 'banjax', 'banjacks'), WHERE `Body` LIKE '%banjax%'

works as expected

but

UPDATE`gdn_comment`SET`Body`=REPLACE(`Body`,';lol',':lol:'),WHERE`Body`LIKE'%;lol%'

throws an error.

I've looked on the Interwebby, and there is advice to escape the ; or that you don't need to.

I've tried

UPDATE`gdn_comment`SET`Body`=REPLACE(`Body`,'\;lol',':lol:'),WHERE`Body`LIKE'%\;lol%'

and variations thereof, with no luck.

If anyone has any suggestions I'd be grateful.

It's not mission critical, but I would like to know how to do it if possible.

Thanks

Kaspar

Spaces are missing in the query-example for ;lol

(I have no pre-knowledge to share but am always fresh to do a search)

I ran into Contains instead of Like

https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-2017

I noticed:

Enclose a prefix term in double quotation marks ("") and add an asterisk (*) before the ending quotation mark, so that all text starting with the simple term specified before the asterisk is matched.

The clause should be specified this way: CONTAINS (column, '"text*"')

I may be totally wrong but it seem worth a try?

whu606

@Kaspar

Thanks for replying.

I think the spaces are just missing from me posting up the code, since in phpMyAdmin I simply swapped out the terms of the successful search with the unsuccessful one.

I think the query is failing earlier than the WHERE clause.

If I make it

UPDATE `gdn_comment` SET `Body`= REPLACE (`Body`, 'banjax', 'banjacks'),  WHERE INSTR(`Body`, 'banjax') > 0

I get a successful result, but not if I change to ;lol etc.

Kaspar

Spaces: Assumed so.

What if you use '";lol"' instead of ';lol'?

Kaspar

Btw

"throws an error."

Which error?

whu606

#1064 - it seems to be a generic error code that can be caused by a wide variety of issues.

whu606

It is definitely the ; which is causing the issue.

Running

UPDATE `gdn_comment` SET` Body` = REPLACE(`Body`,'lol',':lols:'), WHERE `Body` LIKE '%lol%'

works fine, but adding in ;lol OR %;lol% fails. (Any spacing errors are down to formatting on here.)

I assume mySQL is interpreting it as ending the code.

Kaspar

Prevoisly suggested:

What if you use '";lol"' instead of ';lol'

' " " '

Or

' ' ' '

*****

https://bioticssupport.natureserve.org/support/solutions/articles/201031-insert-update-allow-the-special-characters-and-in-sql-insert-and-update-statements

"Semicolon:

SET SQLTERMINATOR OFF is supposed to remove the special meaning of ;, but it doesn't seem to work. Instead, change the SQL Terminator to another symbol, e.g. "~":

SET SQLTERMINATOR ~ (or use SET SQLT ~ for short)

To turn semicolon back on use:

SET SQLTERMINATOR ON"

*****

https://stackoverflow.com/questions/48228945/how-to-handle-special-characters-in-sql-server-select-query/48229497#48229497

Suggestion:

declare @str varchar(100)=';lol'
UPDATE `gdn_comment` SET `Body`= REPLACE (`Body`, '%'+@str+'%', ':lol:'), WHERE `Body` LIKE '%'+@str+'%'

Kaspar

So, I am now near my PC - so I could play around/test stuff myself.

Have just tested this (used the 'search and replace' -GUI to make it):

UPDATE `GDN_Comment` SET `Body` = REPLACE(`Body`, ';lol', ':lol:') WHERE `Body` LIKE '%;lol%' COLLATE utf8mb4_bin

That worked

Note that there is no comma (,) before WHERE - and ofc the added COLLATE characterset

whu606

Thanks so much for taking the time to do that.

I'll be a go at that tomorrow.

Shadowdare

Have a look at this if you're using phpMyAdmin: https://stackoverflow.com/a/44303704

Kaspar

https://open.vanillaforums.com/discussion/comment/256376#Comment_256376

👍

I though that was the issue aswell but I tested the above query with and without ; as delimiter - no change.

x00

have you tried command line mysql client?