add a prefix to fields, where second character is a number

Forum Moderators: open

Message Too Old, No Replies

add a prefix to fields, where second character is a number

nigelt74

12:54 am on Jun 9, 2010 (gmt 0)

Hi all

Ok the situation is this, i have a number of records stored in a database, like this

1, /123456.jpg
2, /abcdef.jpg
3, /234556.jpg

I need to add a prefix to those images that start with a number, but I can't figure out how to do it,

I wrote the following script

UPDATE `zen_products` SET `products_image` = CONCAT('/numerical', `products_image`)
where `products_image` REGEXP '^[^[:alpha:]]'

But unfortunately I had forgotten about the forward slash that prefixes the fields, and I cannot work out how to add that in to my regex, as I keep breaking it

Any help would be greatly appreciated

nigelt74

11:59 pm on Jun 10, 2010 (gmt 0)

Ok Problem solved, not elegantly but it worked

I ran a query to remove the first character '/' of all the strings

then ran my query slightly modified

UPDATE `zen_products` SET `products_image` = CONCAT('numerical/', `products_image`)
where `products_image` REGEXP '^[^[:alpha:]]'

The ran a query to add a prefix of '/' to all the fields