1
+----+---------------+-------+
| id | link          | title |
+----+---------------+-------+
| 1  | v_156399569   | abc   |
+----+---------------+-------+
| 2  | y_u0inSsAoFU4 | xyz   |
+----+---------------+-------+

Query:

SELECT if(substr(link, 1, 2)='y_', 
                'https://www.youtube.com/watch?v='substr(link,3), 
                'https://vimeo.com/'substr(link,3)) as link, `title`
FROM `video`
WHERE `reg_id` = '101'  

Desired Output:

+-------------------------------------------+-------+
| link                                      | title |
+-------------------------------------------+-------+
| https://www.youtube.com/watch?v=156399569 | abc   |
+-------------------------------------------+-------+
| https://vimeo.com/u0inSsAoFU4             | xyz   |
+-------------------------------------------+-------+

In the above query I can't use substr(link,3) function when expression is true. After v= I wanna get a sub string value of link column in the table.

The above query fire some error. So please help me to solve this issue. Answers will be appreciated.

2
  • So the question is basically how to concatenate 2 strings? Jul 19 '17 at 8:45
  • @ypercubeᵀᴹ yes. The below answer works.
    – Shihas
    Jul 19 '17 at 8:51
3

You can use CONCAT function to add this two strings on this way:

create table video(link varchar(200), title varchar(200));
insert into video values ('y_LjhCEhWiKXk', 'Bruno Mars - Just The Way You Are [OFFICIAL VIDEO]');
select title,
       if(substr(link, 1, 2)='y_', 
          concat('https://www.youtube.com/watch?v=', substr(link,3)), 
          concat('https://vimeo.com/', substr(link,3))) as link
from   video;
title                                              | link                                       
:------------------------------------------------- | :------------------------------------------
Bruno Mars - Just The Way You Are [OFFICIAL VIDEO] | https://www.youtube.com/watch?v=LjhCEhWiKXk

dbfiddle here

0
0

This is just a modification of McNets solution a bit simplified and switching to Standard SQL syntax when possible (substr -> substring, and if -> case) also supported by MySQL (only || instead of concat doesn't exist). Maybe more characters to key in, but easier to port to other DBMSes:

select title,
   concat(case substring(link from 1 for 2)
             when 'y_' then 'https://www.youtube.com/watch?v='
             when 'v_' then 'https://vimeo.com/'
                  -- easy to extend for more sites
             else 'unknown site ' -- just in case
          end
         ,substring(link from 3)) as link
from video

Of course this would be simpler if the link column was properly normalized.

2

Not the answer you're looking for? Browse other questions tagged or ask your own question.