We help IT Professionals succeed at work.

Sorting strings & numbers in SQL query

Medium Priority
329 Views
Last Modified: 2012-05-11
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



Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Software Engineer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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
Rajkumar GsSoftware Engineer

Commented:
Glad I could help you
Raj
CERTIFIED EXPERT

Commented:
select
      *
from
      cartrows
order by
      right(replicate('0',256)+col,256)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.