Splitting column Values in MYSQL--
--there is A column 'published_on' in epoker_news_in_media table Having specified date like jan 21,2015 in Varchar datatype and have to split it in three diffrerent columns ' Month (varchar)' ,'Date (int), and 'Year(int)' . Query is given below using Cast/Convert Function
Select left( epoker_news_in_media.published_on ,3 )as Month ,cast( SUBSTRING_INDEX(SUBSTRING_INDEX( epoker_news_in_media.published_on , ',', 1),' ',-1) as signed) as Date,
Convert(right (epoker_news_in_media.published_on ,4 ),Signed Integer) as Year,
from 1stspade_old.epoker_news_in_media
----Casting In MYSQL
exp
insert into employeedetail(id,Name,Address) Values(cast('2015' as signed), cast(6 as char),'nanital')----id and Name are int and Varchar type resp. So we are using Cast Function simulteneously.
----Casting in ungmeters or Arguments (MYSQL)
value
The value to convert to another datatype.
type
The datatype that you wish to convert value to. It can be one of the following
Value -- Description------
DATE --- Converts value to DATE type, which has a date portion only.
Format is--- 'YYYY-MM-DD'.
Supported range is '1000-01-01' to '9999-12-31'.
DATETIME --- Converts value to DATETIME type, which has both date and time portions.
Format is 'YYYY-MM-DD HH:MM:SS'.
Supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
TIME ---- Converts value to TIME type, which has a time portion only.
Format is 'HH:MM:SS'.
Supported range is '-838:59:59' to '838:59:59'.
CHAR --- Converts value to CHAR type, which is a fixed length string.
SIGNED --- Converts value to SIGNED type, which is a signed 64-bit integer.
UNISIGNED Converts value to UNSIGNED type, which is an unsigned 64-bit integer.
BINARY Converts value to BINARY type, which is a binary string.
Example--
Let's look at some MySQL CAST function examples and explore how to use the CAST function in MySQL.
With DATE
The first CAST function example shows how to cast a value to a DATE type. For example:
mysql> SELECT CAST('2014-02-28' AS DATE);
Result: '2014-02-28'
This CAST example takes the value '2014-02-28' and casts it as a DATE datatype.
With DATETIME
This CAST function example shows how to cast a value to a DATETIME type. For example:
mysql> SELECT CAST('2014-02-28 08:14:57' AS DATETIME);
Result: '2014-02-28 08:14:57'
This CAST example takes the value '2014-02-28 08:14:57' and casts it as a DATETIME datatype.
With TIME
This CAST function example shows how to cast a value to a TIME type. For example:
mysql> SELECT CAST('08:14:57' AS TIME);
Result: '08:14:57'
This CAST example takes the value '08:14:57' and casts it as a TIME datatype.
With CHAR
This CAST function example shows how to cast a value to a CHAR type. For example:
mysql> SELECT CAST(125 AS CHAR);
Result: '125'
This CAST example takes the value 125 and casts it as a CHAR datatype with the value of '125'.
With SIGNED
This CAST function example shows how to cast a value to a SIGNED type. For example:
mysql> SELECT CAST(4-6 AS SIGNED);
Result: -2
This CAST example takes the value 4-5 and casts it as a SIGNED datatype with the value of -2.
With UNSIGNED
This CAST function example shows how to cast a value to an UNSIGNED type. For example:
mysql> SELECT CAST(4-6 AS UNSIGNED);
Result: 18446744073709551614
This CAST example takes the value 4-5 and casts it as an UNSIGNED datatype with the value of 18446744073709551614.
With BINARY
This CAST function example shows how to cast a value to a BINARY type. For example:
mysql> SELECT CAST('4' AS BINARY);
Result: '4'
This CAST example takes the value '4' and casts it as a BINARY datatype with the binary string value of '4'.