Mysql select query string operation

Question

I have created following query, the query is inject _thumb before image extension, but the query is not working at all conditions,

 SELECT `user_id`,`image`, 
 CONCAT(SUBSTRING_INDEX(`image`,'.',1),'_thumb.',SUBSTRING_INDEX(`image`,'.',-1))
 as `image_thumb` 
 FROM `user_details`

Result

user_id |image      |image_thumb
--------|-----------|--------------------
    1   |gk1.JPG    |gk1_thumb.JPG
    2   |Tulips.jpg |Tulips_thumb.jpg
    3   |vnc.1.jpg  |vnc_thumb.jpg
    4   |NULL       |NULL
    10  |NULL       |NULL

user_id 3 row image_thumb not expected

I want to result:

    3   |vnc.1.jpg  |vnc.1_thumb.jpg

Can you please anyone help me to create right query?

Answer 1

Use a combination of SUBSTRING, LENGTH and SUBSTRING_INDEX:

SELECT `user_id`,
       `image`, 
       CONCAT(
         SUBSTRING(`image`,
                   1,
                   LENGTH(`image`) - LENGTH(
                                         SUBSTRING_INDEX(`image`,'.',-1)
                                     )-1
         ),
         '_thumb.',
         SUBSTRING_INDEX(`image`,'.',-1)
) as `image_thumb` 
FROM
     `user_details`

(I tried to format the query as readable as possible)

Answer 2

Maybe something like:

> select @extension:=substring_index('vnc.1.JpG','.',-1) ext,
replace('vnc.1.JpG',@extension,concat('_thumb.',@extension)) thumb;
+-----+------------------+
| ext | thumb            |
+-----+------------------+
| JpG | vnc.1._thumb.JpG |
+-----+------------------+

Or replacing variable usage with multiple calls to substring_index:

> select replace('vnc.1.JpG',
                 substring_index('vnc.1.JpG','.',-1),
                 concat('_thumb.',substring_index('vnc.1.JpG','.',-1))) thumb;
+------------------+
| thumb            |
+------------------+
| vnc.1._thumb.JpG |
+------------------+