Solved

# Sort column numerically when type is nvarchar

Posted on 2004-11-03
348 Views
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
Question by:TRACEYMARY

LVL 26

Expert Comment

select * from <yourtable>
ORDER BY CAST(SUB_ID AS INT)
0

LVL 5

Expert Comment

select columns
from table
where isnumeric(sub_id) = 1
order by cast(sub_id as decimal(10,2))
0

LVL 50

Expert Comment

order by right("000" + sub_id ,3)
0

LVL 7

Author Comment

Oh it could be Alpha i.e a

0

LVL 26

Expert Comment

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)

this is merely a comment on Lowfatspread's suggestion.
0

LVL 7

Author Comment

What is N for
order by right(N'000' + sub_id ,3)
0

LVL 8

Accepted Solution

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

LVL 7

Author Comment

i tired
order by right(N'000' + WORKORDER_sub_id, 10)

but i get
0
1
10
11
2
0

LVL 50

Expert Comment

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

### Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.