Post reply

How to convert datatype varchar to int?

  • November 28, 2008 at 7:49 am

    #126172

    From this table, if I use Order By then Route is not arranged in a proper way for VA state. You can see below.

    Create table #Temp ( State varchar(5), Route varchar(10), Rank int)

    Insert Into #Temp (State,Route,Rank) Values ( 'NY','B',1)

    Insert Into #Temp (State,Route,Rank) Values ( 'NY','B',2)

    Insert Into #Temp (State,Route,Rank) Values ( 'NY','F',1)

    Insert Into #Temp (State,Route,Rank) Values ( 'NY','F',2)

    Insert Into #Temp (State,Route,Rank) Values ( 'NY','F',3)

    Insert Into #Temp (State,Route,Rank) Values ( 'NY','K',1)

    Insert Into #Temp (State,Route,Rank) Values ( 'VA','1',1)

    Insert Into #Temp (State,Route,Rank) Values ( 'VA','1',2)

    Insert Into #Temp (State,Route,Rank) Values ( 'VA','2',1)

    Insert Into #Temp (State,Route,Rank) Values ( 'VA','3',1)

    Insert Into #Temp (State,Route,Rank) Values ( 'VA','10',1)

    Insert Into #Temp (State,Route,Rank) Values ( 'VA','10',2)

    Insert Into #Temp (State,Route,Rank) Values ( 'VA','20',1)

    Select * from #Temp

    Order By State, Route, Rank

    When I execute the output will be:

    NY B 1

    NY B 2

    NY F 1

    NY F 2

    NY F 3

    NY K 1

    VA 1 1

    VA 1 2

    VA 10 1

    VA 10 2

    VA 2 1

    VA 20 1

    VA 3 1

    But I need it in

    NY B 1

    NY B 2

    NY F 1

    NY F 2

    NY F 3

    NY K 1

    VA 1 1

    VA 1 2

    VA 2 1

    VA 3 1

    VA 10 1

    VA 10 2

    VA 20 1

    Then I thought varchar must be converted into int when State is equal to VA. So I used case statement but it gives me error.

    Select * from #Temp

    Order By State,

    Case

    When State='NY' then Route

    Else convert(int,Route)

    End, Rank

    Can you please help me out?

  • November 28, 2008 at 8:30 am

    #904828

    You cannot convert it to an int if there are CHARS in the cloumn as well.

    FYI, the code for converting to an int is

    CAST(YourField as Int)

  • November 28, 2008 at 8:35 am

    #904832

    Yes, i got it. But do you think is there any other way?

  • November 28, 2008 at 8:40 am

    #904837

    Shree, try this:

    ORDER BY State, RIGHT('000000000'+LTRIM(Route), 10), Rank

    Try it without the LTRIM too, it may not be necessary.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 28, 2008 at 8:42 am

    #904842

    You can't treat the Route column as both an int and a varchar

    To get the sort right you can try this, it won't work for all situations but it may work for what you want

    Select * from #Temp

    Order By State,

    Case

    When State='NY' then Route

    WHEN State='VA' then CASE

    WHEN LEN(Route) = 1 THEN ' ' + Route

    ELSE Route

    END

    End, Rank

  • November 28, 2008 at 8:44 am

    #904844

    But of course ChrisM's is more elegant 😉

  • November 28, 2008 at 9:37 am

    #904877

    Thank you all. You made my problem look so simple. Onca again thank you so much.

  • November 29, 2008 at 4:42 am

    #905067

    http://sqlblogcasts.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

    Madhivanan

    Failing to plan is Planning to fail

  • November 29, 2008 at 6:54 am

    #905079

    Madhivanan (11/29/2008)

    http://sqlblogcasts.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

    Ummm... that's cool, but most of those methods don't work on alpha-numeric columns.

    Chris did it right.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply