1

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
mickmackusa
  • 43,625
  • 12
  • 83
  • 136
john
  • 11,311
  • 40
  • 131
  • 251
  • The value always starts with `hello_world` and ends with a number or are there other patterns? – Robert Kock Oct 24 '18 at 16:05
  • Yes the values always start with hello_world and ends with a number. – john Oct 24 '18 at 16:07
  • Is there any way, we can achieve that in sql ? – john Oct 24 '18 at 16:09
  • it's because it orders each character in turn, and the 1 (first character in the 10, for example) is considered less than 4 (a character in the same position in another string), so that comes first (because all the characters previous to that are equal in both strings). Of course since it's a varchar column, it doesn't see them directly as numbers, they're just individual characters within the string. – ADyson Oct 24 '18 at 16:12
  • Depending on your situation you could either a) change the values so that you use leading zeros for the lower values - e.g. `helloworld04` instead of `helloworld4`. Then 0 will be less than 1 and it will sort as you might have intended. Or b), create a new "order" column (of `int` data type) in the database specifically to store the number representing the order of the rows, and use that in your Order By clause. Or c) you could try extracting the number values from the existing string, casting as a number and then ordering by that resulting value.But it might impact performance in large tables – ADyson Oct 24 '18 at 16:14
  • `hello_world04` and `hello_world05` would solve your problem. – Eric Oct 24 '18 at 16:42

5 Answers5

1

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','' ))
Randy
  • 16,480
  • 1
  • 37
  • 55
1

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

order by length(name), name
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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;
Robert Kock
  • 5,795
  • 1
  • 12
  • 20
0

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 );
Prince Francis
  • 2,995
  • 1
  • 14
  • 22
  • Can you make a fiddle ? It will be easy to visualize a problem then. – john Oct 24 '18 at 18:23
  • Thanks for sharing the link. Here is fiddle which I have created from your answer https://www.db-fiddle.com/f/t9WE9DuAHKHEJn2Qgw5tqg/2 Its sorting on the basis of repl. In the real picture I don't want to display repl. Is there any way we can hide ? – john Oct 24 '18 at 19:44
  • Edited the post and fiddle also – Prince Francis Oct 25 '18 at 03:57
-1

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)
Ramji
  • 375
  • 2
  • 14
  • If you know the user is using MySQL, why would you provide an answer in SQL Server (especially using proprietary functions such as `PATINDEX`)? – Zack Oct 24 '18 at 17:07