mysql replace number value from select to another string

Question

i would like to replace value like:

1106,1107,1108

from select query to a string link like:

http://something.com/img/1/1/0/6/1106.jpg,http://something.com/img/1/1/0/7/1107.jpg,http://something.com/img/1/1/0/8/1108.jpg

can it be done in mysql query?

Have a go with look at MySQL's REPLACE (http://www.w3resource.com/mysql/string-functions/mysql-replace-function.phphttp://www.w3resource.com/mysql/string-functions/mysql-replace-function.php) — Stuart, Apr 06 '16 at 11:12

score 0 · Accepted Answer · edited May 23 '17 at 11:52

Assuming the image names have variable length, I think you'll need to write a stored function to implement this natively in MySQL, there's no obvious built-in function.

The example below will take 1106 and convert it to http://something.com/img/1/1/0/6/1106.jpg. To parse multiple image IDs like 1106,1107,1108 you'd need to extend it to insert the path again every time it finds a comma, or (better) select the results out of the database in a way that is not comma-separated.

DELIMITER //
CREATE FUNCTION TO_IMAGE_PATH(id VARCHAR(255), path VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC NO SQL
BEGIN
    DECLARE output VARCHAR(255) DEFAULT path;
    DECLARE position INT DEFAULT 1;
    WHILE position <= LENGTH(id) DO
        SET output = CONCAT(output, SUBSTRING(id, position, 1), '/');
        SET position = position + 1;
    END WHILE;
    SET output = CONCAT(output, id, '.jpg');
    RETURN output;
END//
DELIMITER ;

SELECT TO_IMAGE_PATH('1106', 'http://something.com/img/');
-- Output: http://something.com/img/1/1/0/6/1106.jpg

You might prefer to pass in the jpg extension, or hard-code the initial path.

While this does work, this seems like an example of a problem which might be better solved in another programming language after you have selected out your results.

If all of the image IDs are exactly 4 digits long, you could do the simpler (but less elegant)

SELECT CONCAT(
    'http://something.com/img/',
    SUBSTRING(field_name, 1, 1), '/',
    SUBSTRING(field_name, 2, 1), '/',
    SUBSTRING(field_name, 3, 1), '/',
    SUBSTRING(field_name, 4, 1), '/',
    field_name, '.jpg');

Again, you'd need to work out how to select the values out so they aren't comma-separated. In general, if you're storing values comma-separated in your database, then you shouldn't be.

wow. i used the function path! it works like a charm! thanks Matt!! — yongcc, Apr 06 '16 at 14:25

mysql replace number value from select to another string

1 Answers1