SQL sort by containing string

Question

I am trying to sort by whether '/us/' is in a url. I currently have:

SELECT is_live, store_url from my_table where item_id = 1306085 
ORDER BY (CASE WHEN store_url LIKE '%/us%' THEN 1 ELSE 0 END) DESC

Is there a cleaner way to do this, or is the above the only way? Conceptually, I'm looking for something a bit more readable, such as:

ORDER BY store_url.contains('/us/') DESC

You could create a stored function or an user defined function in MySQL. — Frank J
– Frank J, Commented Mar 7, 2016 at 20:55
@FrankJ thanks for the idea, that is a neat approach. Could you please show in an answer how that would be done with the above question? — David542
– David542, Commented Mar 7, 2016 at 20:56
You don't need the case to convert, true is treated as 1 and false as 0 for sorting purposes anyway. So ORDER BY store_url LIKE '%/us%' DESC should be fine... if you want it to look more like a function call you can use the INSTR function... though that returns the actual found position, so you would want the ordering on INSTR(...)=0 ASC to prevent unwanted ranking by location in string. — Uueerdo
– Uueerdo, Commented Mar 7, 2016 at 20:56

MaxU - stand with Ukraine · Accepted Answer · 2016-03-07 21:44:02Z

2

you may try this:

SELECT is_live, store_url from my_table where item_id = 1306085 
ORDER BY instr(store_url, '/us/');

or:

SELECT is_live, store_url from my_table where item_id = 1306085 
ORDER BY instr(store_url, '/us/') > 0 ;

SQLFiddle

edited Mar 7, 2016 at 21:44

answered Mar 7, 2016 at 20:56

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Comments

Frank J · Accepted Answer · 2016-03-07 21:46:36Z

1

You could create a function:

CREATE FUNCTION mycontains (s text, matchText VARCHAR(255))
RETURNS INT DETERMINISTIC
RETURN (CASE WHEN s LIKE CONCAT("%", matchText, "%") THEN 1 ELSE 0 END);

and use it like

SELECT is_live, store_url from my_table where item_id = 1306085 
ORDER BY mycontains(store_ul, "/us/") DESC

Note: There is already a contains function, I think it works with geometric objects, that is why I named this mycontains.

edited Mar 7, 2016 at 21:46

answered Mar 7, 2016 at 21:09

Frank J

1,70614 silver badges19 bronze badges

6 Comments

MaxU - stand with Ukraine Over a year ago

Have you heard of "Occam's razor": [Plurality must never be posited without necessity] ? Link: en.wikipedia.org/wiki/Occam%27s_razor#Ockham ;)

Frank J Over a year ago

@MaxU: Well, your solution introduces a possibly not wanted order by returning the index, so you cannot further sort unless you amend your solution by equalizing all results greater than 0. So this does something different.

MaxU - stand with Ukraine Over a year ago

OP doesn't care whether each of those parts (0's and 1's) will be sorted or not... And your solution doesn't take into account slashes - /us/ ;)

Frank J Over a year ago

@MaxU: Since you amended your solution I agree that yours is the better way to go, and upvoted yours. Out of curiosity though, slashes seem to be working fine for me, what do you mean?

MaxU - stand with Ukraine Over a year ago

strings like aaa_us_bbb will be sorted incorrectly as OP wanted to check: "whether /us/ is in a url". Now (after you edited it) it will work properly. So +1

|

Collectives™ on Stack Overflow

SQL sort by containing string

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related