SQL Order By Specific String in a Column

Question

I was recently tasked to put a column in order base on a specific string. Bellow is the screenshot of my table:

How can I sort the picture column in the following order:

2017/June/06/169799_1_kYFJf.jpg
2017/February/18/169799_2_rkJ2d.jpg
2017/January/12/169799_3_23wKj.jpg
2017/March/18/169799_4_iMbw7.jpg
2017/May/18/169799_5_HwDBf.jpg

I tried ORDER BY picture DESC but it didn't work.

Unfortunately I am not allowed to add a new column, like maybe column "sort", which will define the sort number of the image.

James Joyce Alano · Accepted Answer · 2017-06-06 03:59:54Z

3

try this one:

SELECT *
FROM test
ORDER BY SUBSTRING(picture, LOCATE(pid, picture) + LENGTH(pid) + 1, 1) DESC

Fiddle: http://sqlfiddle.com/#!9/7a334c/3

It's a combination of various string functions readily available for MySQL

For the SUBSTRING function, it takes the string you want to get a substring of, the starting position, and the length of substring you wish to extract..

So lets break down the three parameters we used for the SUBSTRING function

1.) picture - the column that we need to extract the numbers from

2.) LOCATE(pid, picture) + LENGTH(pid) + 1 - we are locating the string containing the pid substring from the picture column, then adding the length of the pid itself so we get the "underscore" character, then adding 1 again to get the "number" for sorting.. this will return the position of the number after the pid and "underscore" character

3.) 1 - This represents the length that you need to "cut" or "extract" from the string, this will pose a problem if you have 2 digits to extract... but for now, we can work with this..

http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php

edited Jun 6, 2017 at 3:59

answered Jun 6, 2017 at 3:50

James Joyce Alano

7033 silver badges13 bronze badges

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

3 Comments

Carl Over a year ago

Your code works!, but it looks a little strange to me. Could you explain it a little bit please? especially this part of your code: SUBSTRING(picture, LOCATE(pid, picture) + LENGTH(pid) + 1, 1); sorry newby here

James Joyce Alano Over a year ago

I updated the answer, will update more later.. but this will hopefully give you a brief insight to what I did

Carl Over a year ago

Hi, you're correct, i've tried more than 10 items and the order appeared incorrect. Can you update the query to sort more than 10 items please?

zedfoxus · Accepted Answer · 2017-06-06 04:53:02Z

2

You can try something like this:

Example data:

select * from test;
+--------+----------------------------------------+
| pid    | picture                                |
+--------+----------------------------------------+
| 169799 | 2017/March/18/169799_4_iMbw7.jpg       |
| 169799 | 2017/February/18/169799_2_jadsflkjasdf |
| 169799 | 2017/June/06/169799_1_jasfd;ads        |
| 169799 | 2017/May/18/169799_5_jasfd;ads         |
| 169799 | 2017/June/12/169799_10_jasfd;ads       |
| 169799 | 2017/January/12/169799_3_iMbw7.jpg     |
+--------+----------------------------------------+

Sort by date

select * from test order by str_to_date(substring_index(picture,'/',3), '%Y/%M/%d') desc;
+--------+----------------------------------------+
| pid    | picture                                |
+--------+----------------------------------------+
| 169799 | 2017/June/12/169799_10_jasfd;ads       |
| 169799 | 2017/June/06/169799_1_jasfd;ads        |
| 169799 | 2017/May/18/169799_5_jasfd;ads         |
| 169799 | 2017/March/18/169799_4_iMbw7.jpg       |
| 169799 | 2017/February/18/169799_2_jadsflkjasdf |
| 169799 | 2017/January/12/169799_3_iMbw7.jpg     |
+--------+----------------------------------------+

Explanation:

substring_index(picture,'/',3) will split text by / and output the first 3 item
str_to_date(..., '%Y/%M/%d') converts the result to a date based on the given format. %M is month name

EDIT

Sort by number

select * from test
order by cast(
  substring_index(substring_index(picture, '_', 2), '_', -1)
  as unsigned
);
+--------+----------------------------------------+
| pid    | picture                                |
+--------+----------------------------------------+
| 169799 | 2017/June/06/169799_1_jasfd;ads        |
| 169799 | 2017/February/18/169799_2_jadsflkjasdf |
| 169799 | 2017/January/12/169799_3_iMbw7.jpg     |
| 169799 | 2017/March/18/169799_4_iMbw7.jpg       |
| 169799 | 2017/May/18/169799_5_jasfd;ads         |
| 169799 | 2017/June/12/169799_10_jasfd;ads       |
+--------+----------------------------------------+

Explanation:

substring_index(picture, '_', 2) will split text by _ and output the first 2 item
substring_index(..., '_', -1) picks the last item, which is the number

References:

edited Jun 6, 2017 at 4:53

answered Jun 6, 2017 at 3:37

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

11 Comments

Carl Over a year ago

Hi, thanks for the quick answer. I need it ordered by the numbers after the string "169799_", it should be: 169799_1, 169799_2, 169799_3, 169799_4, 169799_5. Any idea how I achieve this?

zedfoxus Over a year ago

Ah, okay. Sure, let me try something.

zedfoxus Over a year ago

Do you want to order by date in the picture or do you want to order by the number?

Carl Over a year ago

just the numbers

Carl Over a year ago

Thanks for patiently working on this. Yes the casting works!, please do update answer so I can mark as correct answer.

|

SeanJohn · Accepted Answer · 2017-06-06 03:54:03Z

2

Hi so you can do a case statement but I couldn't figure out the logic you wanted it to order by ? Is it the 1, 2, 3, 4, 5 in the middle in the filename ? You can also use a regular expression to just look at those numbers and order by that. I don't know regular expressions off the top of my head though. What I gave you will work but its not the best answer as it's a lot of overhead in the query with poor performance.

order by
(Case when picture like '%_1_%' then '1'
when picture like '%_2_%' then '2'
when picture like '%_3_%' then '3'
when picture like '%_4_%' then '4'
when picture like '%_5_%' then '5' else '0' end)

Just keep adding the order you want it to sort by depending on how many numbers that go but again this might be a lot of overhead in a query and take a bit of time. Not sure how faster a regular expression can be to parse out just the number after the underscores to sort by. So performance might be an issue with my answer.

answered Jun 6, 2017 at 3:54

SeanJohn

1971 gold badge3 silver badges11 bronze badges

1 Comment

Carl Over a year ago

Yes I do feel like this might not be suitable query for our application as we are currently focusing on optimizing query speed. But thanks so much for the answer!

KevinKirkpatrick · Accepted Answer · 2017-06-06 03:35:25Z

1

In virtually all dialects of SQL, you could do the following:

order by 
    to_number(
        substr(
            picture,
            instr(picture,'_',1,1)+1,
            instr(picture,'_',1,2) - instr(picture,'_',1,1) -1))

Some dialects of SQL (not sure about mysql) offer a friendlier delimited-string parsing function, e.g. "split_part()", which would support the more compact:

order by to_number(split_part(picture,'_',2))

answered Jun 6, 2017 at 3:35

KevinKirkpatrick

1,4541 gold badge10 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL Order By Specific String in a Column

4 Answers 4

3 Comments

Sort by date

Sort by number

11 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Sort by date

Sort by number

11 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related