ordering

Question

I have the values

20a

20a1

20a2

20b

20c

20

22

24

and I want them to be ordered in this order, How can i achieve this

Answer 1

What is your output?

---

A Fan of SSIS, SSRS and SSAS

Answer 2

create table test ( val varchar(20))
insert into test values('29'),('20a'),('20a1'),('20a2'),('20b'),('20c'),('20'),('22'),('24')
select * from test


--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
 
,processTable as (
    select val, 
	col1  as Number_removed_val  
	,Cast(col2 as int)  as letter_removed_val  

    from test 
	Cross Apply (
            select (select C + ''
            from (select N, substring(val, N, 1) C from Nums  
            where N<=datalength(val)) t
            where PATINDEX('%[^0-9]%',C)> 0
            order by N
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
        ) p0 (col1) 
			Cross Apply (
            select (select C + ''
            from (select N, substring(val, N, 1) C from Nums  
            where N<=datalength(val)) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
        ) p1 (col2)  
 
) 
SELECT   val
FROM processTable
Order by Cast(ISNULL(Stuff(val,PATINDEX('%[^0-9]%',val),len(val),'') ,val) as int)
,Number_removed_val,letter_removed_val
 


drop table test

Answer 3

Try the following query. i have jumbled the values just to get the sorting.

create table #temp (value varchar(10))

insert into #temp
select '20a'

insert into #temp
select '20a1'

insert into #temp
select '20a2'

insert into #temp
select '22'

insert into #temp
select '24'

insert into #temp
select '20b'

insert into #temp
select '20c'

insert into #temp
select '20'


select * from #temp order by PATINDEX('%[^0-9]%',value) desc

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. - Kerobin

Answer 4

If you want 20 after 20c, you can adjust the query:

SELECT   val 
FROM processTable
Order by Cast(ISNULL(Stuff(val,PATINDEX('%[^0-9]%',val),len(val),'') ,val) as int)
,isnull(Number_removed_val,'z'),letter_removed_val

Answer 5

Hi Saanah,

 

Please try following script.

 

IF OBJECT_ID('test') IS NOT NULL drop table  test
go 
create table test ( val varchar(20))
insert into test values
('20a'),('20a1'),('20a2'),('20b'),
('20c'),('20'),('22'),('24')

;with cte as (
select *,
case when  PATINDEX('%[^0-9]%',val)= 0 
then val else left(val,PATINDEX('%[^0-9]%',val)-1) end as [val1],
case when  PATINDEX('%[^0-9]%',val)= 0 
then 1 else 0 end [val2],
case when  PATINDEX('%[^0-9]%',val)= 0 
then null else right(val,len(val)-PATINDEX('%[^0-9]%',val)+1) end as [val3]
from test )
select val from cte order by val1,val2,val3
/*
val
--------------------
20a
20a1
20a2
20b
20c
20
22
24
*/

 

Hope it will help you.

 

Best Regards,

Rachel

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

Hi Saanah,

I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

 

Best Regards,

Rachel

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

>> I have the values <<

Why did you fail to post DDL? Your request makes no sense because all we have is a silly picture in ASCII. We now have to put it in the table for you, but if you knew what a table was you would remember that tables have no ordering. This is by definition! It's usually covered in the first chapter of any book on RDBMS and SQL. You really need to read such a book before you try to program again

CREATE TABLE Foobar (foo_string VARCHAR(10) NOT NULL PRIMARY KEY)'
INSERT INTO Foobar
VALUES 
('20a'), ('20a1'), ('20a2'), 
('20b'), ('20c'), ('20'),
('22'),
('24');

>> and I want them to be ordered in this order, How can I achieve this?<<

By using an implicit cursor with complicated logic. Rachel probably gave you the best kludge that's possible with a mess like this. The real answer however is to stop writing databases like this. Can you start over?

This is one of the problems with badly designed DDL. You probably don't spend a lot of time looking at industry standards or ISO documents, but most encoding schemes are fixed length, and try to avoid mixing alpha and numerics. Instead of just inventing things on the fly a good database person actually designs his data. It's just like a good programmer actually plans his code instead of writing kludges. Ideally, we'd like to be able to write a very simple regular expression to guarantee data integrity.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL