Mysql - best way to replace text

Question

I need to replace the following text in one of columns:

#post{some numbers}

to

#post-{some numbers}

What is the best and most efficient way to do so? I have found regex replace for mysql: https://stackoverflow.com/a/38660574/5417374

But wonder if there is more marter way.

are there also `#post{not numbers}` lines? – Nanne Jan 30 '17 at 12:39 — Nanne, Jan 30 '17 at 12:39

score 0 · Accepted Answer · answered Jan 30 '17 at 12:41

0

If this is a one-off, I would just create a script in your language of choice reading all the rows, replacing it with said languages' rexep function of your choice and writing it back.

If this is a regular occurance, I would do this again in code, but before entering it in the database.

I see no specific need to do this in sql.

answered Jan 30 '17 at 12:41

Nanne

64,065
16
119
163

Ok, the only solution i see (maybe a little but slow due to amount of records - over 2 mln). – pwas Jan 30 '17 at 15:30

score 0 · Answer 2 · answered Jan 30 '17 at 12:48

0

Please try this query, I assume "some numbers" as numbers.

select concat(substr("#post{123456}", 1, INSTR("#post{123456}",'{')-1),"-",substr("#post{123456}", INSTR("#post{123456}",'{'),length("#post{123456}")-(INSTR("#post{123456}",'{')-1) ))

answered Jan 30 '17 at 12:48

imsome1

1,182
4
22
37

Mysql - best way to replace text

2 Answers2