Why is a nested cast NOT working in MySQL? (It does using SQL Server)
select cast(cast(myColumn as decimal(5,2)) as int) from myTable
Asked
Active
Viewed 1,873 times
6
juergen d
- 201,996
- 37
- 293
- 362
-
What are you trying to accomplish? What is the purpose of casting as `UNSIGNED`, only then to interpret the resulting data as a signed `INT`? Won't this have the same effect as directly doing `CAST(myColumn AS INT)`? – eggyal Apr 27 '12 at 09:05
-
@eggyal: I changed my example. The question came up trying to find integer numbers in a varchar field and ignoring the decimal numbers like that: `select * from tab where cast(cast(myColumn as decimal(5,2)) as int) = cast(myColumn as decimal(5,2))` – juergen d Apr 27 '12 at 09:09
-
To accomplish that, you'll probably find it easier to test your columns against [regular expressions](http://dev.mysql.com/doc/refman/5.1/en/regexp.html). See [this question](http://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql) for more info. – eggyal Apr 27 '12 at 09:10
-
@eggyal: You are absolutly right. But still I would like to know what to do if a nested cast would be needed. – juergen d Apr 27 '12 at 09:11
-
Indeed. I'm hoping someone else might be able to shine some light on it for you, as the only thing I can think of is to perform the first `CAST` in a subquery and then the second in an outer query. – eggyal Apr 27 '12 at 09:13
2 Answers
3
According to the manual:
CAST(expr AS type)[...]
CONVERT(expr,type)[...]The
typecan be one of the following values:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
So, just follow the manual:
SELECT CAST(CAST(myColumn AS DECIMAL(5,2)) AS SIGNED) FROM myTable
or
SELECT CAST(CAST(myColumn AS DECIMAL(5,2)) AS UNSIGNED) FROM myTable
0
This query is working on the concept of nested cast.
cast(sum(cast(Column_name int )+ cast(Column_name as int)) as bigint) as payment from table_name
