[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Sort column numerically when type is nvarchar

Hi i have in my database

SUB_ID nvarchar(3)

so i end up with    0
                    1
                   10
                   20
                    2

And i need to be able to get 0,1,2,10,20

Any suggestions
0
TRACEYMARY
Asked:
TRACEYMARY
  • 3
  • 2
  • 2
  • +2
1 Solution
 
HilaireCommented:
select * from <yourtable>
ORDER BY CAST(SUB_ID AS INT)
0
 
svidCommented:
select columns
from table
where isnumeric(sub_id) = 1
order by cast(sub_id as decimal(10,2))
0
 
LowfatspreadCommented:
order by right("000" + sub_id ,3)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
TRACEYMARYAuthor Commented:
Oh it could be Alpha i.e a

0
 
HilaireCommented:
Lowfatspread's code is robust and handles both numeric and alpha.
Just use single quotes instead of double quotes, and a N for the string constant to avoid automatic conversion

order by right(N'000' + sub_id ,3)

Please don't accept this comment as an answer,
this is merely a comment on Lowfatspread's suggestion.
0
 
TRACEYMARYAuthor Commented:
What is N for
order by right(N'000' + sub_id ,3)
0
 
sigmaconCommented:
N makes a string a Unicode Character String (like nvarchar). Make sure you cover as many digits as your numbers can be long, e.g. 10 digits if you store equivalents of integers:

order by right(N'0000000000' + sub_id, 10)

the above assumes you have empty strings '' in your column
0
 
TRACEYMARYAuthor Commented:
i tired
order by right(N'000' + WORKORDER_sub_id, 10)

but i get
0
1
10
11
2
0
 
LowfatspreadCommented:
order by right(N'0000000000' + WORKORDER_sub_id, 10)

but you started off saying the column was length 3
so
order by right(N'000' + WORKORDER_sub_id, 3) should work...

if it doesn't

can you do a

select '(' + workorder_sub_id +')'
where workorder_sub_id like '%1%'
order by 1

and post the results

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now