Welcome to WebmasterWorld Guest from 54.160.254.203

Forum Moderators: open

Message Too Old, No Replies

cast a substring in mysql, possible?

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

New User

5+ Year Member

joined:Apr 28, 2010
posts: 1
votes: 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
1:19 pm on Apr 29, 2010 (gmt 0)

Administrator

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

joined:July 31, 2003
posts:12533
votes: 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]
1:28 pm on Apr 29, 2010 (gmt 0)

Administrator

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

joined:July 31, 2003
posts:12533
votes: 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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members