Prashant Prashant - 6 months ago 16
MySQL Question

Replace with IF Condition and LIKE statement, in MySQL

In my database I have a table like this:

id image_url house_id
1 /upload/house-id-52_myimagename1.jpg 52
2 /upload/house_id_52_myimagename2.jpg 52
3 /upload/house-id-52_myimagename3.jpg 52


I want to duplicate it using SQL query, but want to have image name in the image_url column always prefix with "house-id-{house_id}" this format, no matter if it is in underscore format in previous id. IT should be always dash separated for new duplicated results as shown output below:

id image_url house_id
4 /upload/house-id-53_myimagename1.jpg 53
5 /upload/house-id-53_myimagename2.jpg 53
6 /upload/house-id-53_myimagename3.jpg 53


I have made a query for this:

INSERT INTO house_gallery (image_url, house_id)
(SELECT
IF(image_url LIKE '%house_id_%',
replace(image_url, 'house_id_52', 'house-id-53'),
replace(image_url, 'house-id-52', 'house-id-53'))
AS `image_url`, 53 AS house_id
FROM house_gallery WHERE house_id = 52)


But it is not working in case of
house-id-52
. Please check and advise what could be the solution for this, to get dashed url.

Answer

If the pattern is same you can probably use the substring_index function. Consider the following

mysql> select * from image ;
+------+--------------------------------------+----------+
| id   | image_url                            | house_id |
+------+--------------------------------------+----------+
|    1 | /upload/house-id-52_myimagename1.jpg |       52 |
|    2 | /upload/house_id_52_myimagename2.jpg |       52 |
|    3 | /upload/house-id-52_myimagename3.jpg |       52 |
+------+--------------------------------------+----------+

select 
case 
 when 
  substring_index(substring_index(image_url,'/',-1),'_',1) = 'house' 
  then replace(image_url,'house_id_52','house-id-53') 
  else 
   replace(image_url, 'house-id-52', 'house-id-53') 
end  as modified 
from image ;

You will get

+--------------------------------------+
| modified                             |
+--------------------------------------+
| /upload/house-id-53_myimagename1.jpg |
| /upload/house-id-53_myimagename2.jpg |
| /upload/house-id-53_myimagename3.jpg |
+--------------------------------------+

Now lets create the other table

mysql> create table image_copy like image ;
Query OK, 0 rows affected (0.11 sec)

mysql> describe image_copy;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | YES  |     | NULL    |       |
| image_url | varchar(100) | YES  |     | NULL    |       |
| house_id  | int(11)      | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

So the insert query is

insert into image_copy 
select 
id,
case 
 when 
  substring_index(substring_index(image_url,'/',-1),'_',1) = 'house' 
  then replace(image_url,'house_id_52','house-id-53') 
  else 
  replace(image_url, 'house-id-52', 'house-id-53') 
end  as image_url,
'53'  
from image 
where house_id = 52
;

mysql> select * from image_copy ;
+------+--------------------------------------+----------+
| id   | image_url                            | house_id |
+------+--------------------------------------+----------+
|    1 | /upload/house-id-53_myimagename1.jpg |       53 |
|    2 | /upload/house-id-53_myimagename2.jpg |       53 |
|    3 | /upload/house-id-53_myimagename3.jpg |       53 |
+------+--------------------------------------+----------+
3 rows in set (0.00 sec)