SHUBHAM KUMAR 11233004 4D3
EXPERIMENT NO.: 10
AIM:- To Describe MySQL Conversion Functions.
Conversion Function in SQL
In SQL data type conversion is important for effective database management and
accurate query results. Data type conversion ensures that data from different sources or
columns can be correctly interpreted and manipulated, especially when dealing with
different formats like numbers, text, dates, and other data types.
Types of Data Type Conversion in SQL
There are two main types of data type conversion in SQL.
• Implicit Data Type Conversion: This is done automatically by the database
management system (DBMS) when SQL operations involve columns of
different data types. For instance, a string value might automatically be
converted into a numeric type if required by a mathematical opera on.
• Explicit Data Type Conversion: This is done by the user, who specifies the
conversion. This is necessary when SQL cannot automatically convert between
data types, or when more control over the conversion is needed.
Types of Data Conversion
1. Type Conversion Functions (General Data Type Conversion)
• CAST() - Converts One Data Type to Another
SELECT Name, CAST(PhoneNo AS UNSIGNED) AS Phone_Int FROM
Employee;
SHUBHAM KUMAR 11233004 4D3
Fig(02): CAST()
• CONVERT() - Alternative to CAST()
SELECT Name, CONVERT(Salary, CHAR) AS Salary_Char FROM Employee;
Fig(03): Convert()
2. Numeric Conversion Functions (Manipulating Numeric Values)
• FLOOR() - Rounds Down to the Nearest Integer
SELECT Name, FLOOR(Salary) AS Salary_Floor FROM Employee;
Fig(04): Floor()
• FORMAT() - Formats Numeric Values with Commas
SHUBHAM KUMAR 11233004 4D3
Fig(05): Format()
• ABS() - Returns the Absolute Value
SELECT Name, ABS(Salary - 100000) AS Salary_Difference FROM Employee;
Fig(06): ABS()
3. Date and Time Conversion Functions (These functions help format and
manipulate date and me values.)
• DATE_FORMAT() - Convert Date to a Readable String Format
SELECT Name, DATE_FORMAT(DOJ, '%W, %M %d, %Y') AS DOJ_Format FROM
Employee;
Fig(07): DATE_FORMAT()
• STR_TO_DATE() - Convert String to Date
SELECT STR_TO_DATE('12-05-1990', '%d-%m-%Y') AS Converted_Date;
Fig(08): STR_TO_DATE
• EXTRACT() - Extract Parts of a Date
SELECT Name, EXTRACT(YEAR FROM DOB) AS Birth_Year FROM Employee;
SHUBHAM KUMAR 11233004 4D3
Fig(09): Extract()
4. String Conversion Functions (These functions manipulate and convert string
values.)
• REVERSE() - Reverse a String
SELECT Name, REVERSE(Name) AS Reverse_Name FROM Employee;
Fig(10): Reverse()
• BINARY - Convert String to Binary
SELECT Name, BINARY Name AS Name_Binary FROM Employee;
Fig(11): Binary
• CONCAT() - Concatenate Strings
SELECT CONCAT(Name, ' (', Email, ')') AS Employee_Contact FROM Employee;
SHUBHAM KUMAR 11233004 4D3
Fig(12): CONCAT()
• HEX() - Convert String to Hexadecimal
SELECT Name, HEX(Designa on) AS Designa on_Hex FROM Employee;
Fig(13): HEX()
• IFNULL() - Replace NULL with a Default Value
Fig(14): INFULL()
• REPLACE() - Replace Substring in a String
SELECT Name, REPLACE(Designa on, 'Engineer', 'Developer') AS
New_Designa on FROM Employee;
SHUBHAM KUMAR 11233004 4D3
Fig(15): REPLACE()
• CONVERT_TZ() - Converts a date/ me from one me zone to another
SELECT Name, DOJ, CONVERT_TZ(DOJ, 'UTC', 'Asia/Kolkata') AS DOJ_IST
FROM Employee;
Fig(16): CONVERT_TZ
CONCLUSION
In conclusion, MySQL conversion functions play a crucial role in data manipulation
and transformation within a database. These functions allow you to convert values from
one data type to another, enabling flexibility in handling different data formats. Some
commonly used conversion functions in MySQL include CAST(), CONVERT(),
BINARY, and CHAR(). These functions ensure that data can be properly stored,
retrieved, and processed, regardless of the format or type in which it was originally
stored. By utilizing MySQL conversion functions effectively, developers can ensure
better compatibility between different data types, optimize query performance, and
maintain data integrity in the database.
