nicker nicker - 5 months ago 10
MySQL Question

How to do select if else statement in Mysql

Sample Data:

[{url: bing.com},
{url: bing.com/search?q=test},
{url: bing.com/efesdf+sdsdksd}]


I would like to return all url string before '?' or '+', which means it will return bing.com/search for 2nd url and bing.com/efesdf for 3rd, removing anything after '?' or '+'

Thus I tried the code below

SELECT substring(url,1,instr(pattern,'?')-1) FROM testTable;


However doing this url without '?' will be shown as "", is it possible to do a if statement like

if (url.includes('?')){
SELECT substring(url,1,instr(url,'?')-1)
}
else if (url.includes('+')){
SELECT substring(url,1,instr(url,'+')-1)}
}
else{
SELECT URL
}
FROM testTable;


and what if I would like to select * and alter only url?

Please assist,
Thanks.

Answer

Maybe you need this;)

SELECT
    *,
    CASE WHEN LOCATE('?', url) THEN LEFT(url, LOCATE('?', url) - 1)
    WHEN LOCATE('+', url) THEN LEFT(url, LOCATE('+', url) -1)
    ELSE url END AS editedUrl
FROM testTable;

Check SQLFiddle DEMO HERE.

Comments