Edit text in columns

Question

Just wondering if someone can assist me with the following issue.

I have a database in MySQL and I'm using PHPMyAdmin to interact with the database.

So within the table example_table_name I have two columns product_item_short and product_item_long which have URL's in them. Now there is 3000 rows of data and as an example the URL in each starts with data/image/someimage.png.

In both columns I need to remove the data/ and I would like to know how I could so this in SQL.

Many thanks

Answer 1

You can use the SUBSTR() function:

UPDATE your_table 
SET    product_item_short = SUBSTR(product_item_short, 6),
       product_item_long = SUBSTR(product_item_long, 6);

Test case:

CREATE TABLE your_table (
    id int, 
    product_item_short varchar(50), 
    product_item_long varchar(50)
);


INSERT INTO your_table VALUES (1, 'data/image/someimage.png',         'data/image/someimage.png');
INSERT INTO your_table VALUES (2, 'data/other-folder/someimage.png',  'data/other-folder/someimage.png');
INSERT INTO your_table VALUES (3, 'data/no-folder.png',               'data/no-folder.png');
INSERT INTO your_table VALUES (4, 'data/image/path/three-levels.png', 'data/image/path/three-levels.png');

Result after UPDATE:

SELECT * FROM your_table;
+------+-----------------------------+-----------------------------+
| id   | product_item_short          | product_item_long           |
+------+-----------------------------+-----------------------------+
|    1 | image/someimage.png         | image/someimage.png         |
|    2 | other-folder/someimage.png  | other-folder/someimage.png  |
|    3 | no-folder.png               | no-folder.png               |
|    4 | image/path/three-levels.png | image/path/three-levels.png |
+------+-----------------------------+-----------------------------+
4 rows in set (0.00 sec)

---

UPDATE:

If you a solution that eliminates the first directory of the path, whether it is data/ or anything-else/, you may want to use the LOCATE() function, as @Frank's suggested in another answer:

UPDATE your_table 
SET    product_item_short = SUBSTR(product_item_short, LOCATE('/', product_item_short) + 1),
       product_item_long = SUBSTR(product_item_long, LOCATE('/', product_item_long) + 1);

Answer 2

UPDATE example_table
SET product_item_short = SUBSTRING(product_item_short,6),
    product_item_long = SUBSTRING(product_item_long,6);

Answer 3

UPDATE tablename
SET product_item_short = REPLACE(product_item_short, 'data/image/someimage.png', 'newurl/something.png');

Answer 4

If you want to be more flexible than what Daniel described in his answer, you can use something like

SUBSTR(product_item_short, LOCATE('/', product_item_short) + 1)

which would cut off everything after the first slash.