Order By nvarchar

I have a scenario to sort on an nvarchar (50) field and I need to keep any changes to the sproc in the ORDER BY clause if possible. This field will contain strings such as...

abc-217c, abc-15a, abc-9a, abc-7b, abc-17ar, etc...

The issue I'm having is when the records are sorted, they are returned as...

abc-15a, abc-17ar, abc-217c, abc-7b, abc-9a,etc...ordering numerically on the first numeric character in the string ie, 1,1,2,7,9)

However, I need the numeric component to be treated as a whole number and order in this fashion...

abc-7b, abc-9a, abc-15a, abc-17ar, abc-217c (7,9,15,17,217, etc)

I feel pretty sure that this issue has come up before...can anybody provide a working example that would provide a simple(or not so simple) solution?

Hello my friend,

The SQL to do this would be very complicated. If I were you, I would create extra fields in the table where these codes come from to hold parts of the code. For example: -

FullCode Name Number Section

abc-217c abc 217 c

By breaking this down, you can still select the FullCode and order by Name, Number, Section so it comes out right.

Kind regards

Scotty

|||

try code below:

create

table #test(aaanvarchar(50))

insert

into #test

Values

('abc-217c')

insert

into #test

Values

('abc-15a')

insert

into #test

Values

('abc-9a')

insert

into #test

Values

('abc-7b')

insert

into #test

Values

('abc-17ar')

select

*,substring(aaa, 1,charindex('-',aaa))+

case

whenisnumeric(substring(aaa,charindex('-',aaa)+1,4))= 1then'0'+substring(aaa,charindex('-',aaa)+1,4)whenisnumeric(substring(aaa,charindex('-',aaa)+1,3))= 1then'00'+substring(aaa,charindex('-',aaa)+1,3)whenisnumeric(substring(aaa,charindex('-',aaa)+1,2))= 1then'000'+substring(aaa,charindex('-',aaa)+1,2)whenisnumeric(substring(aaa,charindex('-',aaa)+1,1))= 1then'0000'+substring(aaa,charindex('-',aaa)+1,1)

else

''

end

+

case

whenisnumeric(substring(aaa,charindex('-',aaa)+1,4))= 1thensubstring(aaa,charindex('-',aaa)+5,50)whenisnumeric(substring(aaa,charindex('-',aaa)+1,3))= 1thensubstring(aaa,charindex('-',aaa)+4,50)whenisnumeric(substring(aaa,charindex('-',aaa)+1,2))= 1thensubstring(aaa,charindex('-',aaa)+3,50)whenisnumeric(substring(aaa,charindex('-',aaa)+1,1))= 1thensubstring(aaa,charindex('-',aaa)+2,50)

else

''

end

fieldToOrderBYfrom #test

order

by fieldToOrderBY

drop

table #test

Good luck

sql