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?

Lorenzo Marcon · Accepted Answer · 2014-03-26 17:50:22Z

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)

answered Mar 26, 2014 at 17:50

Lorenzo Marcon

8,2395 gold badges40 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

atxdba · Accepted Answer · 2014-03-26 17:41:04Z

0

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 |
+------------------+

answered Mar 26, 2014 at 17:41

atxdba

5,2165 gold badges27 silver badges30 bronze badges

1 Comment

Girish Over a year ago

thanks for quick reply, but this query would only work fine with user_id 3 and other rows result wrong,

Collectives™ on Stack Overflow

Mysql select query string operation

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related