HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Any mySQL/MariaDB gurus around? Need help with query
whu606
MVP
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
Tagged:
0
Comments
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?
@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
I get a successful result, but not if I change to ;lol etc.
Spaces: Assumed so.
What if you use '";lol"' instead of ';lol'?
Btw
"throws an error."
Which error?
#1064 - it seems to be a generic error code that can be caused by a wide variety of issues.
It is definitely the ; which is causing the issue.
Running
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.
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:
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):
That worked
Note that there is no comma (,) before WHERE - and ofc the added COLLATE characterset
Thanks so much for taking the time to do that.
I'll be a go at that tomorrow.
Have a look at this if you're using phpMyAdmin: https://stackoverflow.com/a/44303704
Add Pages to Vanilla with the Basic Pages app
👍
I though that was the issue aswell but I tested the above query with and without ; as delimiter - no change.
have you tried command line mysql client?
grep is your friend.