0

Normally, I would run the following to replace text within a table in my MySQL database:

UPDATE post SET pagetext = REPLACE(pagetext, '[TEXT1]', '[TEXT2]')

But how do you replace text that is followed by an alphanumeric set of characters?

This is what I'm trying to replace:

[TEXT:12345678] will be replaced with [TEXT]
[/TEXT:12345678] will be replaced with [/TEXT]

How do I run the above query with some wildcard variables so anything with 

[TEXT:********]

will be replaced with

[TEXT]

Forgive my ignorance, but I would like to run this through phpmyadmin - but how do I rewrite the REPLACE portion to accommodate the variables following the text? Thanks in advance.

Daniel
  • 37
  • 6
  • possible duplicate of [How to do a regular expression replace in MySQL?](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) – Micha Wiedenmann Mar 28 '14 at 11:34
  • Yes, you will have to use regular expression here. – Rahul Mar 28 '14 at 11:36
  • Forgive my ignorance, but I would like to run this through phpmyadmin - but how do I rewrite the REPLACE portion to accommodate the variables following the text? – Daniel Mar 28 '14 at 11:45

1 Answers1

0

Daniel try to do this and you will solve your problem :

  1. Export your table into a .SQL file using the EXPORT function from the phpMyAdmin panel.
  2. Using sed command in linux replace your tags using it's wildcard(.)

  3. Run the replace command:
    cat post_table.sql | sed 's/latex:......../latex/g' > post_table_complete_output.sql

  4. Then import the output file in your database.

Done.

luchaninov
  • 6,792
  • 6
  • 60
  • 75
Up_One
  • 5,213
  • 3
  • 33
  • 65
  • Forgive my ignorance, but I would like to run this through phpmyadmin - but how do I rewrite the REPLACE portion to accommodate the variables following the text? – Daniel Mar 28 '14 at 11:45
  • give me an example of what text you wanna replace with what text ! – Up_One Mar 28 '14 at 11:47
  • [TEXT:########] will be replaced with [TEXT] – Daniel Mar 28 '14 at 11:53
  • this is going to be dynamic or is going to be the same pattern ? – Up_One Mar 28 '14 at 11:57
  • Only those hash tags represent the dynamic data. There are 8 alphanumeric characters followed by the static pattern. Some examples of what I'm trying to replace - [TEXT:12345678] [TEXT:abcdefgh] [TEXT:a1b2c3b4] [TEXT:95720584] [TEXT:3ahfba32] [TEXT:bc92b20c] – Daniel Mar 28 '14 at 11:59
  • Ok. I will try to use that substring within my replace query. Are you on any messenger programs? If I can get this to work, I'm happy to pay you for your help, as I really need to get this to work! Trying it now... – Daniel Mar 28 '14 at 12:32
  • Oh boy. That didn't work. That replaced everything with the output of text. (sigh) – Daniel Mar 28 '14 at 12:51
  • I'm restoring the database (I'm glad I did a backup). When I go into phpmyadmin and run your query, you're right it returns [TEXT] but I need to use it in conjunction with the search and replace the text for a given table within my database. – Daniel Mar 28 '14 at 12:55
  • @Daniel - to start with STOP testing in your working Environment!!! bad practice - replicate that db and play with it !!! – Up_One Mar 28 '14 at 12:56
  • you where missing the where clause ! – Up_One Mar 28 '14 at 13:04