cast a substring in mysql, possible?

Forum Moderators: open

Message Too Old, No Replies

cast a substring in mysql, possible?

bigbear17

7:53 am on Apr 28, 2010 (gmt 0)

hello.
is there a way which i am able to cast/ convert my substrings into integer?
This is what i have been trying to do, apparently, it does not work.

SELECT campaignmonth

CAST(campaignmonth, substring(campaignmonth,1,1)as Jan)

FROM campaign a;

thanks

coopster

1:19 pm on Apr 29, 2010 (gmt 0)

Welcome to WebmasterWorld, bigbear17.

You have your syntax incorrect. CAST in MySQL requires UNSIGNED or SIGNED and the INTEGER part is optional in the syntax. If your campaignmonth column was of type DATE and formatted as yyyy-mm-dd, the query might look like this to get the month portion as an integer value ...

SELECT 
  a.campaignmonth, 
  CAST(SUBSTRING(a.campaignmonth,6,2) AS UNSIGNED INTEGER) AS monthInteger 
  FROM campaign AS a 
;

Resource:
Cast Functions and Operators [dev.mysql.com]

coopster

1:28 pm on Apr 29, 2010 (gmt 0)

Oh, one more thing I was going to mention. When you use the MySQL API (like with a programming language such as PHP), the values are often returned as string anyway. Yes, the value will indeed be an INTEGER value, but the actual type cast may most likely be STRING. Just a heads up.