This article is an effort to discuss SQL Cast and SQL Convert functions as a follow-up to previous articles, in which we’ve discussed several SQL tips such as SQL Date, SQL Coalesce, SQL Union, SQL Join, SQL Like, SQL String etc. Show
Sometimes we need to convert data between different SQL data types. In addition to working with data, there are some built-in functions can be used to convert the data. So let’s take a closer look at the SQL conversion functions SQL CAST and SQL CONVERT in detail. IntroductionIn order to perform operations or comparisons or transformation between data in a SQL Server database, the SQL data types of those values must match. When the SQL data types are different, they will go through a process called type-casting. The conversion of SQL data types, in this process, can be implicit or explicit. The data type conversion functions commonly used specially to meet the data standards of the target objects or systems. We are in the world dealing with heterogeneous data. Note: A walk-through of high-level concepts of data management is discussed in this article SQL string functions for Data Munging (Wrangling). Sometimes the data types of the data need to get converted to other data types for calculations or transformation or processes or to meet destination data formats. For example, when we multiply decimal type data with an integer, the data undergoes internal transformation and implicitly converts an integer to decimal data type and the result is of decimal data. Implicit and Explicit SQL conversionWhen we deal with two values which are same in nature but different data types, behind the scenes, the database engine convert the lower data type values to higher data type before it could go ahead with the calculation. This type is known as an implicit conversion. On the other hand, we have explicit conversions where you either call a SQL CAST or SQL CONVERT function to change the data type. You can refer to the SQL date format Overview; DateDiff SQL function, DateAdd SQL function and more article for examples. Syntax:
exp datatype len style SQL Convert and Cast data conversion chartThe following conversion chart is a quick reference sheet for data type conversion Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017 Example 1: SQL Implicit conversion with numbersIn the following example, two random numbers 5 and 212345 are added and results are displayed.
We see that the two values are of different data types, the database engine implicitly converted the int value to a bigint before it did the addition. And then it could process the addition operation as they become the same data type. Example 2: SQL Implicit conversion with charactersIn the following example, we’ll add two character data types. Let us create variables @a character data type and assign a value ‘5’ and @b with a value ‘2’.
In the output, first, we get the values of the variables ‘a’ and ‘b’, second and result of the calculation that is both the values together. With the character values are in place, the database engine implicitly decided to concatenate both values and display 52 as the output of the calculation. Example 3: How to perform an Explicit conversion using SQL CastIn the following example, we’ll see how to force the conversion from one data type to another. Let us take an above example and convert the input values to numeric type and then add those values.
The SQL CAST function takes two arguments. First, the data we want to process, in this case, the data is ‘5’ and ‘2’ and are of char field, and then how you want to process it, or how we want to CAST it. In this case, you want to CAST it as an integer field. In the output, you notice that the input character values are converted and also added those values and result is of integer type. Example 4: Advanced error handling using TRY_CASTIn the following example, we’ll further dissect the values of data types using SQL cast function and SQL try_cast function. In the following SQL, the string value ‘123’ is converted to BIGINT. It was able to convert because of the nature of the data. Let us take a look at the second example, the string value ‘xyz’ is used in the expression for the SQL cast function. When you’re doing this, you will see an error stating “Error Converting data type varchar to bigint”. When you’re doing the data analysis with various data sources, we usually get such type of values in the source data. So in order to handle this type of scenarios, you can use the SQL try_cast function. In the third line of SQL, we pass the string as an input value, after execution, we’ve not received an error but end up in getting a NULL value as an output. The NULL value can be further simplified using SQL ISNULL function or SQL coalesce function. Note: You can refer to the article Using the SQL Coalesce function in SQL Server for more information.
Example 5: Explained style code in CONVERT functionsThe following example converts hiredate field to different available styles of the SQL convert function. The SQL convert function first starts with a data type, and a comma, and then followed by an expression. With Dates, we also have one other argument that we can supply, that’s called a SQL style code. You can get a listing of these in the article CAST and CONVERT (Transact-SQL) technical documentation.
Let’s go ahead execute the aforementioned SQL. Now we can see the different output is being displayed on using style parameters within the SQL convert function. You can see that the hiredate field converts the date field into different styles.
Example 6: The differences and similarities between CAST and CONVERTIn the following example, you can see that the use of CAST and CONVERT in the following T-SQL to compare the execution times. Let us run the following T-SQL to use SQL CAST function
Now, run the same T-SQL with SQL CONVERT function in place.
Quick tips:
Example 7: Style code in the calculation using CONVERTIn this example, you’ll list rate field from “HumanResources.EmployeeHistory” table of Adventureworks2016 database. The second column in the SQL uses the SQL CONVERT function with the data type char(10) and the expression rate data multiply it by 1000. The first SQL, the style code is not used but for the second and third SQL, the style code is set to 1.
In the output, we can see that the query with the style code of 1 returns a text output with a comma separator at the thousands position. The query with the style code 0 results in the text output with no comma separator.
Wrap UpSo far, we talked about the SQL cast, SQL try_cast and SQL convert functions and how you can convert between strings, integers, and the date-and-time values. We also looked into the SQL CONVERT function with the style-code. We walked through some examples of the implicit and explicit SQL conversion functions. And we understood the difference between SQL CAST and SQL CONVERT functions. You also see a conditional expression is used with SQL ISNULL along with the SQL try_cast, and again, if I had done that without using the try part of that, it would have failed instead of returning a value. This is how you can cast values, which is pretty standard, with this additional level, and also and errors more gracefully. That’s all for now… Hope you enjoyed reading this article. Feel free to comment below.
I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram How do I convert an int to a string in MySQL?MySQL CAST() Function
The CAST() function converts a value (of any type) into the specified datatype.
What is CAST () in MySQL?The MySQL CAST() function is used for converting a value from one datatype to another specific datatype. The CAST() function accepts two parameters which are the value to be converted and the datatype to which the value needs to be converted.
How do you convert BIGINT to float?math. BigInteger. floatValue() converts this BigInteger to a float value. If the value returned by this function is too big for a magnitude to represent as a float then it will be converted to Float.
What is BIGINT in MySQL?A large integer. The signed range is -9223372036854775808 to 9223372036854775807 . The unsigned range is 0 to 18446744073709551615 . SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE .
|