Welcome to WebmasterWorld Guest from 54.146.239.96

Forum Moderators: open

Message Too Old, No Replies

cast a substring in mysql, possible?

     

bigbear17

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

5+ Year Member



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)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month