[RESOLVED] Microsoft SQL ORDER BY help

big_nerd

I am having a problem,

I need to ORDER BY a "Reference number" field in my MS SQL DB,

The only problem is, it is a varchar as it contains ref#'s like 1a, 22b, 43c.

Obviously, with a straight ORDER BY I get a sort like 1, 10, 11a, 11b, 2, 21, etc

I tried "CAST(SUBSTRING(ref, 1, 2) AS Integer)", however the lengths of the strings are different, theirfor I will get an error, "Can't convert 8a" etc.

Any ideas on how I can sort this properly?

big.nerd

big_nerd

I tried this as well


SELECT * FROM partlist 
  WHERE (model = '39XDV')
    ORDER BY CASE WHEN (ref != 'N/A') THEN 
    CASE 
      WHEN (LEN(ref) = 3) THEN CAST(SUBSTRING(ref, 1, 2) AS Integer) 
      WHEN (LEN(ref) = 2) THEN CAST(SUBSTRING(ref, 1, 1) AS Integer) 
      WHEN (LEN(ref) = 1) THEN ref 
    END 
END 
ASC

And it is actually worse than "ORDER BY ref"

tomhath

SELECT * FROM partlist 
  WHERE (model = '39XDV')
    ORDER BY 
	CASE WHEN ref = 'N/A' or ref is null or datalength(ref) = 0 THEN 0
	     WHEN PATINDEX ('%[^0-9]%', ref) = 0 then convert(int,ref)
	     ELSE convert(int,substring(ref,1,PATINDEX('%[^0-9]%',ref)-1))
	END

liquorvicar

Would it be easy to pull all the data into PHP and use a natural sort?

big_nerd

Wow,

Thank you!

You are a genious! Any recommendations on a good SQL book so that I may be capable of creating such expressions?

big.nerd

tomhath

I like "The Guru's Guide to Transact-SQL" by Ken Henderson