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

whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP
edited March 2019 in General Banter

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:

Comments

  • KasparKaspar Moderator

    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?

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP

    @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.

  • KasparKaspar Moderator

    Spaces: Assumed so.


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

  • KasparKaspar Moderator

    Btw

    "throws an error."

    Which error?

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP

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

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP
    edited March 2019

    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.

  • KasparKaspar Moderator

    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+'%'
    


  • KasparKaspar Moderator

    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

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP

    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

  • KasparKaspar Moderator

    👍

    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.

Sign In or Register to comment.