sort on a number string MS sql

xav056
xav056 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
if all values in the string column can be converted to a number..

select Cast(stringcolumn as int)
order by 1

Author

Commented:
No some of them are actual strings
Top Expert 2009

Commented:
SELECT * FROM [tablename] WHERE IsNumeric(StringColumn) = 1 ORDER BY [IntegerColumn], CAST(StringColumn As INT)
Might not work, but worth a try...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
try this out.


DECLARE @tbl TABLE (IntCol int ,         StrCol VARCHAR(50)) 
INSERT INTO @tbl 
SELECT 2 ,'3'
UNION SELECT 3,'30One'
UNION SELECT 3,'30'
UNION SELECT 3,'4'
UNION SELECT 3,'35'
UNION SELECT 3,'5'
 
SELECT  intcol
      , Strcol
FROM    @tbl
ORDER BY intcol
      , CASE WHEN ISNUMERIC(Strcol) = 1 THEN CONVERT(INT , strcol)
             ELSE 1
        END 

Open in new window

awking00Information Technology Specialist

Commented:
>>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.)?

Author

Commented:
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

Commented:
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

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial