CODEWITHSUNDEEP
mysqlsql
faq
faq

Reputation: 3076

Mysql query that replaces a unknown number in a path

Lets say that you have the following stored in table:

{2:22}{4:5}{34:4}

I what to delete {4:5} from this string but the system dosent know what the number after the ":" is just the first one. The query looks something like this:

UPDATE tbl SET this = REPLACE(this,'{4:??}','') WHERE id = 1;

What do i need to put in ?? place to return the following result?

{2:22}{34:4}

Upvotes: 0

Views: 27

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Here's one way to do it using LEFT, SUBSTRING, LOCATE and REPLACE:

update yourtable 
set yourcolumn = 
    replace(yourcolumn,
        Left(
            Substring(yourcolumn, 
                 Locate('{4:',yourcolumn),
                 Length(yourcolumn)),
        Locate('}',Substring(yourcolumn, 
                 Locate('{4:',yourcolumn),
                 Length(yourcolumn)))), 
        '')

SQL Fiddle Demo

Upvotes: 1

Related Questions