How to convert datatype varchar to int?

Question

Post reply

How to convert datatype varchar to int?

Shree-903371

SSCommitted

Points: 1930
More actions
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:
NYB1
NYB2
NYF1
NYF2
NYF3
NYK1
VA11
VA12
VA101
VA102
VA21
VA201
VA31
But I need it in
NYB1
NYB2
NYF1
NYF2
NYF3
NYK1
VA11
VA12
VA21
VA31
VA101
VA102
VA201
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?

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

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)

Shree-903371 SSCommitted Points: 1930 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Tom257 SSChampion Points: 11543 More actions · Answer 4

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

Tom257 SSChampion Points: 11543 More actions · Answer 5

Tom257

SSChampion

Points: 11543

November 28, 2008 at 8:44 am

#904844

But of course ChrisM's is more elegant 😉

Shree-903371 SSCommitted Points: 1930 More actions · Answer 6

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

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

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)