asked on

sort on a number string MS sql

Hello
I have two columns in a table one of them is an integer and the second is a string
I want to do by select order by Integer column, String column.
However for various reaso the string column would have numberic value
When I do the sort I would like to get a sort based on integer and not string values
I.e lets say I have th following data
Integer Column StringColumn
2 3
3 30
3 4
3 35
3 5
Iif I apply the order by abopve
I will get
2 3
3 30
3 35
3 4
3 5

I would like to get
2 3
3 4
3 5
3 30
3 35

How can I achecive the above

Thanks

Ralph_Avery

if all values in the string column can be converted to a number..

select Cast(stringcolumn as int)
order by 1

xav056

ASKER

No some of them are actual strings

VBClassicGuy

SELECT * FROM [tablename] WHERE IsNumeric(StringColumn) = 1 ORDER BY [IntegerColumn], CAST(StringColumn As INT)
Might not work, but worth a try...

ASKER CERTIFIED SOLUTION

mazher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

>>No some of them are actual strings<<
So how do you want to deal with them in your order by (i.e. alphabetically first or last, or convert to some value like 0 or skip them entirely, etc.)?

xav056

ASKER

Can you please explain how this is working?
CASE WHEN ISNUMERIC(Strcol) = 1 THEN CONVERT(INT , strcol)
ELSE 1
END
I am getting the results sorted by alpha order then numeric order,

2      abc
2      bcd
2      3
2      4
2      5
2      30
2      40
2      333

is it possible to get them
like this

2      3
2      4
2      5
2      30
2      40
2      333
2      abc
2      bcd

Thanks

mazher

This is another 500 point answer.

You just have to replace with 1 to 99999999999999 so that no highest value can bee such this amount so every time string will come after numeric.

SELECT  intcol
      , Strcol
FROM    @tbl
ORDER BY intcol
      , CASE WHEN ISNUMERIC(Strcol) = 1 THEN CONVERT(INT , strcol)
             ELSE 9999999999999
        END

Open in new window

xav056

ASKER

thanks