CODEWITHSUNDEEP
sqlmysqlsortingsql-order-bynatural-sort
john
john

Reputation: 11669

Naturally sort by column value in SQL

I am working on the SQL query in which I want to sort the string on the basis of numbers.

I have one column (Column Name is Name) table in which there are multiple fields. On using ORDER BY NAME, it prints in the following way:

hello_world
hello_world10
hello_world11
hello_world12
hello_world13
hello_world14
hello_world15
hello_world4
hello_world5

For the above query, I have used ORDER BY NAME; but it doesn't seem to print on the basis of numbers.

Problem Statement:

I am wondering what SQL query I need to write or what changes I need to make in my SQL query above so that it prints everything on the basis of numbers, the o/p should be this:

hello_world
hello_world4
hello_world5
hello_world10
hello_world11
hello_world12
hello_world13
hello_world14
hello_world15

Upvotes: 1

Views: 167

Answers (5)

Prince Francis
Prince Francis

Reputation: 3097

we can order it using replace and cast methods. I tried the following query

select Name, cast(REPLACE(Name, 'hello_world', '') as UNSIGNED ) as repl from Users order by repl;

To generage sample data

CREATE TABLE Users (
    Name varchar(255) NOT NULL
);

insert into Users(Name) values
('hello_world'),
('hello_world4'),
('hello_world5'),
('hello_world10'),
('hello_world11'),
('hello_world12'),
('hello_world13'),
('hello_world14'),
('hello_world15')
;

EDIT query without replaced column,

    select City from Persons order by cast(REPLACE(City, 'hello_world', '') as UNSIGNED );

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think the simplest solution for this particular case (where all the values have the same prefix) is:

order by length(name), name

Upvotes: 1

Robert Kock
Robert Kock

Reputation: 6008

Try this:

SELECT   name,
         CASE WHEN REGEXP_INSTR(name, '[0-9]') = 0 THEN 0
              ELSE CAST(SUBSTR(name, REGEXP_INSTR(name, '[0-9]')) AS INT)
         END AS progressive
FROM     my_table
ORDER BY progressive;

Upvotes: 0

Ramji
Ramji

Reputation: 385

Though the question is about mysql.

I tried in sql server.

create table #t1 (id varchar(100));

insert into #t1 (id) values ('Pq1'),('pq3'),('pq2')

select * from #t 
order by 
CAST(SUBSTRING(id + '0', PATINDEX('%[0-9]%', id + '0'), LEN(id + '0')) AS INT)

Upvotes: -1

Randy
Randy

Reputation: 16677

you want a numeric ordering, then you need to create a numeric value to order on.

currently you have strings.

if the pattern is true, then you can use a combination of string manipulation to trim off the first characters, which should leave only numbers, then use TO_NUMBER() to convert for the ordering

something like

select name 
from mytable
order by to_number( replace( name, 'hello_world','' ))

Upvotes: 1

Related Questions