CODEWITHSUNDEEP
mysqlregexsed
DtyCFx
DtyCFx

Reputation: 61

Search/replace variable text in database

I need to replace some text in a database column. example: replace [youtube]zWg85ALiCUY[/youtube] with //youtube.com/watch?v=zWg85ALiCUY

How do I do it properly?

I've tried dumping the database and running the following command.

sed -i -E "s/\[youtube\](.+)\[\/youtube]/https:\/\/www\.youtube\.com\/watch\?v=\1/" dump.sql

it didn't replace all instances properly. Any other better way? I'm not particularly good with regExp.

Update: Some of the posts have multiple [youtube] BBCode in it.

[youtube]zWg85ALiCUY[/youtube] some text... [youtube]huawda5hy[/youtube]

Each of them needs to be updated.

Upvotes: 0

Views: 134

Answers (2)

kaivalya kari
kaivalya kari

Reputation: 13

Run the following against the database:

update table
set column_name = replace(column_name, '[youtube]zWg85ALiCUY[/youtube]', '//youtube.com/watch?v=zWg85ALiCUY')

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

You can do it in pure MySQL code and without using REGEX.

Query

SELECT 
 REPLACE(REPLACE(col, '[youtube]', '//youtube.com/watch?v='), '[/youtube]', '')
FROM (
  SELECT '[youtube]zWg85ALiCUY[/youtube]' AS `col`
) 
 records

Result

REPLACE(REPLACE(col, '[youtube]', '//youtube.com/watch?v='), '[/youtube]', '')  
--------------------------------------------------------------------------------
//youtube.com/watch?v=zWg85ALiCUY

Upvotes: 2

Related Questions