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



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


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.




  • Options
    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


    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?

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


    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.

  • Options
    KasparKaspar Moderator

    Spaces: Assumed so.

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

  • Options
    KasparKaspar Moderator


    "throws an error."

    Which error?

  • Options
    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.

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

    It is definitely the ; which is causing the issue.


    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.

  • Options
    KasparKaspar Moderator

    Prevoisly suggested:

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

    ' " " '


    ' ' ' '




    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:





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

  • Options
    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

  • Options
    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.

  • Options

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

    Add Pages to Vanilla with the Basic Pages app

  • Options
    KasparKaspar Moderator


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

  • Options

    have you tried command line mysql client?

    grep is your friend.

Sign In or Register to comment.