mysql - SQL find and replace with wildcard -

starting position: have sql-db, contains several tables hunderts or tousends of entries. in several fields (columns) there urls, should shortened (to extract of original url) , added new parameter. thing is, there «find & replace» do:

1. find (old) urls inside fields/columns of tables (stand-alone or inbetween text)…

   http://www.domain.com/click?p(abcdef)a(123456)url(http://www.url.com)

2. … , replace old url (new) url:

   http://www.url.com/?at=uvwxyz

… as can see need wildcard inside url-parameter … like http://www.domain.com/click?p(abcdef)a(123456)url(*) because old url ends ) , need extract of old url (the second url) plus new url-parameter.

• the url inside ( , ) in old url can vary … there maybe hunderts or tousands of different urls.
• the urls in several fields in several tables … inside , surrounded text , not stand-alone (see table below).
• there more one field/columns per entry none, 1 or more one urls per field/column should changed.

table example:

+----------+---+-------------------+---------------+---+--------------+--------------+ | entry_id | … | field_id_1        | field_ft_1    | … | field_id_199 | field_ft_199 | +----------+---+-------------------+---------------+---+--------------+--------------+ | 21364    | … | blabla url blabla | null          | … | none         | none         | | 21363    | … | text url text     | text url text | … | none         | url          | | …        | … | text url text url | null          | … | url text     | none         | +----------+---+-------------------+---------------+---+--------------+--------------+ 

field/column value example:

the new «app of week» is named «<a href="http://www.domain.com/click?p(abcdef)a(123456)url(https://itunes.apple.com/ch/app/popagraph/id587595362?mt=8)">popagraph</a>». app , , bla bla bla <a href="http://www.domain.com/click?p(abcdef)a(123456)url(https://itunes.apple.com/ch/app/popagraph/id587595362?mt=8)">kostenlos</a>. in ios store there «editors choise» like: «<a href="http://www.domain.com/click?p(abcdef)a(123456)url(https://itunes.apple.com/ch/app/the-room-two/id667362389?mt=8)">the room two</a>» (chf 3.00). , more text bla bla bla. 

update: rewrote whole question because of lack of details

to find last url portion of old string, use substring_index , substr functions.

example:

set @old_value :=           'http://www.domain.com/click?p(abcdef)a(123456)url(http://www.url.com)'; select @url := substr( @u := substring_index( @old_value, '(', -1 )                1                 ( length( @u ) - 1 )         ) url 

this return

+--------------------+ | url                | +--------------------+ | http://www.url.com | +--------------------+ 

to update record using new url value, have use concat append query parameters url.

example:

update my_table      set url_col = concat( @url, '/?at=uvwxyz' )     url_col = @old_value     -- ,  -- add more conditions if required     -- ... ... 

refer to:

• mysql: substring_index( str, delim, count )
  • returns substring string str before count occurrences of delimiter delim. if count positive, left of final delimiter (counting left) returned. if count negative, right of final delimiter (counting right) returned. substring_index() performs case-sensitive match when searching delim.