Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Sorting strings & numbers in SQL query

TableName: cartrows
FieldName: sortorder and datatype : varchar
Data: 1 , 2 , 10 , 2 ,5 , A , 1 , B , 2

When I write this query : SELECT * FROM cartrows order by sortorder
Result I got  this : 1, 1, 10, 2, 2, 2, 5, A, B

Expected result : 1, 1, 2, 2, 2, 5, 10, A, B
Can anyone please help me with this sorting?

Thanks,
Andrishelp



0
andrishelp
Asked:
andrishelp
2 Solutions
 
radcaesarCommented:
Is this the one?

DECLARE @t TABLE (col1 VARCHAR(10))

INSERT INTO @t
SELECT 'qA10'
UNION ALL
SELECT 'qA256'
UNION ALL
SELECT 'qA54'
UNION ALL
SELECT 'qA53776'
UNION ALL
SELECT 'qB11'
UNION ALL
SELECT 'qB12'
UNION ALL
SELECT 'qBA11'
UNION ALL
SELECT 'qBA12'

SELECT col1 FROM @t
ORDER BY LEFT(col1,PATINDEX('%[0-9]%',col1)-1), -- alphabetical sort
CONVERT(INT,SUBSTRING(col1,PATINDEX('%[0-9]%',col1),LEN(col1))) -- numerical sort
0
 
Rajkumar GsSoftware EngineerCommented:
Try this query.
select * from YourTable 
order by case isnumeric(YourColumn) when 1 then cast(YourColumn as int) 
else ASCII(YourColumn) end

Open in new window


Raj
0
 
andrishelpAuthor Commented:
Thanks both of you for your quick response.

radcaesar:
SELECT col1 FROM @t
ORDER BY CONVERT(INT,SUBSTRING(col1,PATINDEX('%[0-9]%',col1),LEN(col1))) -- numerical sort

The above query worked but sort order is "A, B, 1, 1, 2, 2, 2, 5, 10

When I tried this LEFT(col1,PATINDEX('%[0-9]%',col1)-1) -- alphabetical sort , I got this error "Invalid length parameter passed to the SUBSTRING function."

RajkumarGS:
Thanks Raj! It worked in the sortorder that I have mentioned in my post.
Expected result : 1, 1, 2, 2, 2, 5, 10, A, B
0
 
Rajkumar GsSoftware EngineerCommented:
Glad I could help you
Raj
0
 
ZberteocCommented:
select
      *
from
      cartrows
order by
      right(replicate('0',256)+col,256)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now