0

My friend ask me few days ago to change few of her database records, but the problem is, it is not just a few records, its about 1000 records, i dont have enought time and nerves to do it manually. It is possible to change it with some kind of regular expression?

I exported database table to XML for better view, here is fragment of it:

 <table name="rozdzialy">
        <column name="nrrozdzialu">1</column>
        <column name="link">http://www.4shared.com/file/97583406/1770dfa5/AAA_vol_1_ch_1_MiA.html</column>
        <column name="link2">AAA/[MiA] AAA vol. 1 ch. 1.zip</column>
        <column name="idtomu">14</column>
    </table>
    <table name="rozdzialy">
        <column name="nrrozdzialu">1</column>
        <column name="link">http://www.4shared.com/file/96401389/bd0b05e2/7th_Period_is_a_Secret_vol_1_ch_1_MiA.html</column>
        <column name="link2">7th Period is a Secret/[MiA] 7th Period is a Secret vol. 1 ch. 1.zip</column>
        <column name="idtomu">17</column>
    </table>

The "link1" is a record with old link, "link2" is place for new (other records are empty). "link1" contain all data that is need to create new link. For example: .../file/97583406/1770dfa5/AAA_vol_1_ch_1_MiA.html new link for that address will look like: "AAA/[MiA] AAA vol. 1 ch. 1.zip

It is possible to do it?

migu
  • 11
  • 1
  • 3
  • afaik there's no simple way for regexreplace in mysql. maybe you can find some solutions here: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql (especially here: https://launchpad.net/mysql-udf-regexp) – bukart Dec 29 '12 at 15:11

1 Answers1

0

If you just want to replace the entire URL:

UPDATE rozdzialy SET link = link2

Or, if you want to replace the filename part whilst retaining the preceeding hsot/path:

UPDATE rozdzialy SET link = CONCAT(
  LEFT(link, CHAR_LENGTH(link) - CHAR_LENGTH(SUBSTRING_INDEX(link, '/', -1))),
  link2
)
eggyal
  • 122,705
  • 18
  • 212
  • 237