SQL Logic to order text with number?

Question

This is a follow up question to an earlier post. I have a column with stings & numbers that I would like to order in sequential order.

Say the customer_id is something like: cust-1-2, cust-10-1, cust-2-1, cust-1-1, cust-3-1

I want to order it by the first number in the string, and then by the second. How can I do this?

I want my result to be ordered like: cust-1-1, cust-1-2, cust-2-1, cust-3-1, cust-10-1

Do you know that they are always numbers? – Gordon Linoff Oct 27 '20 at 00:17 — Gordon Linoff, Oct 27 '20 at 00:17
@GordonLinoff yes it's always that format: -- – nz426 Oct 28 '20 at 18:25 — nz426, Oct 28 '20 at 18:25

score 4 · Answer 1 · answered Oct 26 '20 at 23:47

4

If the format of the string is consistent, you can use split_part():

order by 
    (split_part(customer_id, '-', 2))::int,
    (split_part(customer_id, '-', 3))::int

answered Oct 26 '20 at 23:47

GMB

1 Answers1