CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Himansh Gautam
Himansh Gautam

Reputation: 19

Numeric Values not getting sorted by using substring in SQL server 2005

My table has data as below and when I am selecting the data using order by on the basis of first two characters of string it is not getting sorted properly.

1 - Property Damage 
11 - Legal Expenses
Delivery    
12 - Computer 
14 - Directors 
2 - Business

Below is the expected output:

1 - Property Damage 
2 - Business
11 - Legal Expenses
12 - Computer 
14 - Directors 
Delivery

I am using following query

Select column from table 
order by Substring(column,1,2);

Result set is like :

1 - Property Damage 
11 - Legal Expenses
12 - Computer 
14 - Directors 
2 - Business 
Delivery

My understanding is, that it is sorting on the basis ASCII value. Is there any way to get the required output?

Upvotes: 1

Views: 106

Answers (5)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Split your order in 2, first part is to put columns that doesnt start with a numeric at the bottom - but still sorted while the numeric will still be undetermined (null). Second part will insert spaces so all the numeric has the same length before the first space.

SELECT col
FROM (values
  ('1 - Property Damage '),('11 - Legal Expenses'),('Delivery'),
  ('12 - Computer'),('14 - Directors'),('2 - Business ')) x(col)
ORDER BY
  CASE WHEN col like '[0-9]%' then null else col end, 
  replicate(' ', 10-charindex(' ', col)) + col

Result:

col
1 - Property Damage 
2 - Business 
11 - Legal Expenses
12 - Computer
14 - Directors
Delivery

Upvotes: 1

Hell Boy
Hell Boy

Reputation: 981

Use This Code:

The reason For String datatype It will sort values as like Sorting Alphabetical order i.e)abc

So simply Convert Datatype into Int

Create  TABLE #order
(
    name NVARCHAR(100)

)

INSERT INTO #ORDER VALUES('1 - Property Damage')
INSERT INTO #ORDER VALUES('2 - Business')
INSERT INTO #ORDER VALUES('11 - Legal Expenses')
INSERT INTO #ORDER VALUES('12 - Computer')
INSERT INTO #ORDER VALUES('14 - Directors 
Delivery')


select * from #order

Select name from #order 
order by convert(int,Substring(name,1,2))

Upvotes: 1

Polux2
Polux2

Reputation: 602

A shorter answer 

Select col
from table 
order by cast((Parsename(replace(col,' - ','.'), 2) ) as Int)

Upvotes: 2

PaulFrancis
PaulFrancis

Reputation: 5819

It is because the return of SUBSTRING is a String, unlike Numeric sorting, where 2 follows 1, 11 follows 1 when sorting as Strings. Maybe you could use an IIF?

SELECT 
    [testcolumn]
FROM 
    [AdventureWorks].[dbo].[texttest]
ORDER BY 
    IIF(ISNUMERIC(SUBSTRING(testcolumn, 1, 2)) = 0, 799, CAST(SUBSTRING(testcolumn, 1, 2) AS int)),
    [testcolumn]

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

It is being sorted correctly. It is being sorted as strings rather than numbers. If you want a numeric sort, then you need to sort numbers. Your data structure suggests that you are combining two attributes into a single field. You should think about fixing that.

That said, you can get the ordering you want:

order by (case when col like '_%' then '0' + left(col, 1)
               else left(col, 2)
          end)

The puts a leading "0" in front of the one digit numbers.

Upvotes: 0

Related Questions