SQL Order Chars Numerically

SQL ORDER chars numerically

Try this:

ORDER BY CAST(thecolumn AS int)

SQL Order Chars Numerically - Node Grouping

If there's a limit to the depth¹ of the tree, then you can write something like:

declare @t table (OrdCol varchar(50) not null)
insert into @t (OrdCol) values
('01'),
('01.01'),
('01.02'),
('01.03'),
('01.03.01'),
('01.03.02'),
('01.03.10'),
('01.03.100'),
('01.03.101'),
('01.03.11'),
('01.03.12'),
('01.04'),
('01.04.01'),
('01.04.01.01'),
('01.04.01.02'),
('01.04.01.03'),
('01.04.02'),
('01.04.03'),
('02'),
('02.01')

select OrdCol from
(select OrdCol,CAST('<a><b>' + REPLACE(OrdCol,'.','</b><b>') + '</b></a>' as xml) as xOrd from @t
) t
order by
    xOrd.value('(a/b)[1]','int'),
    xOrd.value('(a/b)[2]','int'),
    xOrd.value('(a/b)[3]','int'),
    xOrd.value('(a/b)[4]','int'),
    xOrd.value('(a/b)[5]','int'),
    xOrd.value('(a/b)[6]','int'),
    xOrd.value('(a/b)[7]','int'),
    xOrd.value('(a/b)[8]','int'),
    xOrd.value('(a/b)[9]','int'),
    xOrd.value('(a/b)[10]','int')

¹Why I asked a clarifying comment to your question about which way "unlimited" children is meant to be interpreted. This query deals with an unlimited number of children at each level, but only deals with a depth of up to 10.

---

Unlimited depth version, works provided that there's at most one leading 0 on any of the numbers:

select OrdCol from
(select OrdCol,CAST(REPLACE(REPLACE('.' + OrdCol + '.','.0','.'),'.','/') as hierarchyid) as hOrd from @t
) t
order by
    hOrd

Which just munges the string until it fits a format castable to hierarchyid, which already performs sorts in the order you expected. Of course, if this is valid, you might consider changing the column datatype to use this type anyway.

SQL order string as number

If possible you should change the data type of the column to a number if you only store numbers anyway.

If you can't do that then cast your column value to an integer explicitly with

select col from yourtable
order by cast(col as unsigned)

or implicitly for instance with a mathematical operation which forces a conversion to number

select col from yourtable
order by col + 0

BTW MySQL converts strings from left to right. Examples:

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123 
'$123'        |  0   /* the left side of the string does not start with a number */

How to sort order by number in SQL?

I am no expert in using regular expression , however using basic one we could achieve it as bellow,

with cte as
(
select '200 DAVID' name
union all
select '100 JOHN'
union all
select '100-1 SHAWN'
union all
select '100-1/1 PETER'
union all
select '100-1/1/1 ALVIS'
union all
select '100-1/1/3 PIRES'
union all
select '100-1/1/10 ROBERT'
union all
select '100-1/1/11 HENRY'
union all
select '100-1/2 BILLY'
union all
select '100-1/2/1 JIOUS'
)
select *
  from(select t.*,cast(regexp_substr(name,'[0-9]+') as unsigned) col1
                 ,cast(regexp_substr(name,'[0-9]+',1,2) as unsigned) col2
                 ,cast(regexp_substr(name,'[0-9]+',1,3) as unsigned) col3
                 ,cast(regexp_substr(name,'[0-9]+',1,4) as unsigned) col4
  from cte t) c
order by col1,col2,col3,col4

First we extract the numeric required for ordering from the string using regexp_substr(name,'[0-9]+') which matches first numeric one more character and returns the first occurrence and with regexp_substr(name,'[0-9]+',1,2) the second occurrence from one more numeric character match and so on...... and then use accordingly in the order by clause.

Demo

Edit:- Solution for MYSQL older versions

select t.id,t.name
from
(
select t.*, cast((case when col1_col2_ref > 0 
                      then 
                        substring_index(modified_name,'-',1)
                  else 
                     modified_name
                  end) as unsigned) col1
          , cast((case when    col1_col2_ref > 0
                           and col3_ref > 0
                       then 
                          substr(modified_name,(col1_col2_ref + 1),(col3_ref - (col1_col2_ref + 1)))
                       when col1_col2_ref > 0
                       then 
                         substr(modified_name,(col1_col2_ref + 1))
                  end) as unsigned) col2
          , cast((case when    col3_ref > 0
                           and col4_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1),(col4_ref - (col3_ref + 1)))
                       when col3_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1))
                  end) as unsigned) col3
          , cast((case when col4_ref > 0
                        then 
                         substr(modified_name,(col4_ref + 1))
                   end) as unsigned) col4
  from
(
select t.*,substring_index(name,' ',1) modified_name
          ,locate('-',name,1) col1_col2_ref
          ,locate('/',name,1) col3_ref
          ,locate('/',name,locate('/',name,1)+1) col4_ref
  from test t
) t
) t
order by col1,col2,col3,col4

Demo2

Order by last 3 chars

This will do it, very simply selecting the right-most 3 characters and ordering by that value ascending.

SELECT *
FROM table_name
ORDER BY RIGHT(name, 3) ASC;

It should be added that as your data grows, this will become an inefficient solution. Eventually, you'll probably want to store the numeric appendix in a separate, indexed integer column, so that sorting will be optimally efficient.

Order by char column numerically

You could use something like:

ORDER BY Cast(regexp_replace(yourcolumn, '[^0-9]', '', 'g') as integer)

SQL Order By while ignoring leading characters

The solution I came up with is similar to the answer given,

I can just cast the column data to an int and sort it the way ie

ORDER BY CAST((ColumnName) AS INT) DESC;

this sucessfully ignores the leading chars and sorts it by the numbers

How do I sort a VARCHAR column in SQL server that contains numbers?

One possible solution is to pad the numeric values with a character in front so that all are of the same string length.

Here is an example using that approach:

select MyColumn
from MyTable
order by 
    case IsNumeric(MyColumn) 
        when 1 then Replicate('0', 100 - Len(MyColumn)) + MyColumn
        else MyColumn
    end

The 100 should be replaced with the actual length of that column.

---

Related Topics