sql how to cast a select query

Question

Is it possible to apply the cast function to a select statement? If yes, how? I have a query that returns a number which I have to use a string to get other info's from another table.

Excellent question. Short and to the point. Enough information to get a reasonable answer - even without the specifics of the particular database server, or whether you are asking about SQL (which would be a reasonable assumption here). Just what I was looking for. — Patrick Moloney, Dec 23 '12 at 14:06
If gives error while using with ORDER BY CAST(`fieldname` as INT) — Vipul Hadiya, Mar 17 '15 at 17:53

score 30 · Accepted Answer · answered Aug 16 '12 at 14:35

30

You just CAST() this way

SELECT cast(yourNumber as varchar(10))
FROM yourTable

Then if you want to JOIN based on it, you can use:

SELECT *
FROM yourTable t1
INNER JOIN yourOtherTable t2
    on cast(t1.yourNumber as varchar(10)) = t2.yourString

answered Aug 16 '12 at 14:35

Taryn

242,637
56
362
405

it gives error while using with ORDER BY CAST(fieldname as INT) – Vipul Hadiya Mar 17 '15 at 17:53
@VipulHadiya If you have a new question, then [ask one](http://stackoverflow.com/questions/ask). – Taryn Mar 17 '15 at 17:54
@blufeet No, i got solution. CAST always gives me error, but if i use (columnName*1) it converts string to float that is what i wanted :) – Vipul Hadiya Mar 18 '15 at 05:51
Congrats this also works for spark SelectExpression method on dataframes. df.selectExpr("colA", "colB as newName", "cast(colC as int)") – Harish Pathak Oct 16 '15 at 12:52

score 4 · Answer 2 · answered Aug 16 '12 at 14:38

4

Yes you can do.

Syntax for CAST:

CAST ( expression AS data_type [ ( length ) ] )

For example:

CAST(MyColumn AS Varchar(10))

CAST in SELECT Statement:

Select CAST(MyColumn AS Varchar(10)) AS MyColumn
FROM MyTable

See for more information CAST and CONVERT (Transact-SQL)

answered Aug 16 '12 at 14:38

Himanshu

31,810
31
111
133

Though the question stated SQL Server 2008, I attempted simalar transactions in mysql, only to find varchar is invalid datatype for cast for mysql see this: http://stackoverflow.com/a/15368838/1815624 – CrandellWS Aug 25 '14 at 19:57

score 2 · Answer 3 · answered Aug 16 '12 at 15:09

I interpreted the question as using cast on a subquery. Yes, you can do that:

select cast((<subquery>) as <newtype>)

If you do so, then you need to be sure that the returns one row and one value. And, since it returns one value, you could put the cast in the subquery instead:

select (select cast(<val> as <newtype>) . . .)

PoeHaH · Answer 4 · 2012-08-16T14:39:31.817

0

If you're using SQL (which you didn't say):

select cast(column as varchar(200)) from table

You can use it in any statement, for example:

select value where othervalue in( select cast(column as varchar(200)) from table)
from othertable

If you want to do a join query, the answer is here already in another post :)

edited Aug 16 '12 at 14:39

answered Aug 16 '12 at 14:33

PoeHaH

1,936
3
28
52

score 0 · Answer 5 · answered Oct 31 '14 at 13:23

0

And when you use a case :

CASE
WHEN TB1.COD IS NULL THEN
    TB1.COD || ' - ' || TB1.NAME
ELSE
    TB1.COD || ' - ' || TB1.NAME || ' - ' || TB.NM_TABELAFRETE
END AS NR_FRETE,

answered Oct 31 '14 at 13:23

Lucas Steffen

1,244
2
10
22

SQL-Server does not support concatenation per "||" – bummi Oct 31 '14 at 13:32

sql how to cast a select query

5 Answers5

Linked