{"id":82,"date":"2017-11-13T20:06:28","date_gmt":"2017-11-13T20:06:28","guid":{"rendered":"http:\/\/eipsoftware.com\/musings\/?p=82"},"modified":"2018-02-19T16:41:27","modified_gmt":"2018-02-19T16:41:27","slug":"datatype-cast-in-t-sql","status":"publish","type":"post","link":"https:\/\/eipsoftware.com\/musings\/datatype-cast-in-t-sql\/","title":{"rendered":"Datatype Cast in T-SQL"},"content":{"rendered":"

\u201cMaking a square peg fit into a round hole\u201d<\/em><\/p>\n

There are many times when dealing with data that you need to transfigure one data set to allow for comparison purposes.<\/p>\n

SQL has built in functions that easily allow us to transfigure data. There are some considerations when using the functions, most importantly understanding what will happen to the data set during the process.<\/p>\n

Today I will talk about the CAST function.<\/p>\n

CAST<\/h4>\n

The T-SQL engine does a relatively excellent job at understanding data types and allowing for the joining of dissimilar types. The method the T-SQL engine uses is explicit and implicit data type conversions. Implicit conversion is the one we are most familiar with and use almost daily. Behind the scenes the T-SQL engine will look at the definition of the fields in the tables and determine if it can convert one field to another data type for comparison purposes. This is the method used when joining two tables where the two fields do not have the same data type but are similar enough that the second field can be converted to the first field\u2019s data type.<\/p>\n

An example of this would be converting a field with a data type of integer to a data type of char (character).<\/p>\n

The second type of data type conversion is explicit conversion. Where by using the CAST function you instruct the T-SQL engine to transfigure the data type from one form or another.<\/p>\n

Building on the previous example mentioned one could write,<\/p>\n

CAST<\/span><\/span><\/span>(<\/span><\/span><\/span>myfieldname <\/span><\/span>AS<\/span><\/span><\/span> CHAR<\/span><\/span>)<\/span><\/span><\/span><\/p>\n

With the syntax for the CAST function is as follows:<\/p>\n

CAST<\/span><\/span><\/span>(<\/b><\/span><\/span><\/span>fieldname<\/i><\/span><\/span><\/span> AS<\/span><\/span><\/span> data_type <\/i><\/span><\/span><\/span>[<\/span><\/span><\/span>(<\/b><\/span><\/span><\/span>length <\/i><\/span><\/span><\/span>)<\/b><\/span><\/span><\/span>]<\/span><\/span><\/span>)<\/b><\/span><\/span><\/span><\/p>\n

With the [(length)] part of the function as optional and only required when the data type requires it. For example,<\/p>\n

CAST<\/span><\/span><\/span>(<\/span><\/span><\/span>myfieldname <\/span><\/span>AS<\/span><\/span><\/span> DECIMAL<\/span><\/span><\/span>(<\/span><\/span><\/span>9<\/span><\/span>,<\/span><\/span><\/span>3<\/span><\/span>))\u00a0<\/span><\/span><\/span>will specify conversion to data type decimal with 9 digits in length and 3 digits to the right of the decimal point.<\/p>\n

Redundancy?<\/h4>\n

If the T-SQL engine will automatically use implicit data type conversion, what is the purpose of using explicit conversion?
\nIt is natural to think that the explicit conversion feature is a redundant capability however, there are times when the T-SQL engine doesn\u2019t perform the conversion in the method you would prefer, or we can use the effects of explicit conversion to join tables or write criteria clauses that normally couldn\u2019t be done.<\/p>\n

PRACTICAL EXAMPLE<\/h4>\n

In the SAP database the field mara.matnr (material number) is stored as an 18 character value. The SAP folks know that some companies will use all numeric values and others will use a combination of alpha and numeric characters in their material numbers. So, the SAP software stores the material number as an 18 character value to accommodate as many scenarios as possible. If you have a material number of 100278, in the SAP database the matnr field is stored as 000000000000100278.<\/p>\n

With that being said, normally when entering in the material number we would want to ensure the leading zeros have been stripped. However not all tables in the SAP database have this operation completed. And some tables the matnr field is stored with all 18 characters. Now when we try to join a table where the matnr field value = \u2018100278\u2019 and the other table has the field value as \u2018000000000000100278\u2019, the two rows won\u2019t join. In addition the T-SQL database engine won\u2019t do a data type conversion because each table has the field specified as a NVARCHAR.<\/p>\n

What to do? What to do?<\/strong><\/em><\/p>\n

Well understanding how the fields data will be transfigured if we CAST the field from NVARCHAR to another data type solves the issue for us. We can CAST the field that has the leading zeros to a FLOAT value.<\/p>\n

FROM\tmara AS m\r\n\tINNER JOIN leadingzerotable AS lzt \r\n\tON\tm.matnr = CAST(CAST(lzt.matnr AS FLOAT) AS NVARCHAR(18))<\/pre>\n
Explanation<\/h4>\n
What does the above example do? It will take the field value of \u2018000000000000100278\u2019 and transfigure the value to a FLOAT data type. When that happens all of the leading zeros are eliminated because the value \u2018000000000000100278\u2019 is the same as 100278. The second cast function will then transfigure the value to an NVARCHAR value which means the field value is change from 100278 to \u2018100278\u2019, (Notice the quotes; it is now a string type data type instead of a numeric data type).<\/p>\n
Caveats<\/h4>\n
You should notice three things about the above example. The first item is I am using the CAST function twice. While not absolutely necessary it is a good practice. If I left off the outer CAST function where I CAST the field back to NVARCHAR the T-SQL database engine would do the same, because the m.matnr field data type is NVARCHAR.<\/p>\n
The second item to notice is I am performing a function during the INNER JOIN statement. Normally performing a function inside of a join or criteria clause can cause major<\/span> performance hits<\/strong>, especially if the value needs to be evaluated. However because the CAST function is a columnar function, there is a minimal performance hit. A columnar function means that the T-SQL engine will perform the function against all values in the column before doing the join. It is a bit more complicated than that, but it is the same thing in principle.<\/p>\n
The last item to notice is that I am not testing if the field value can be CAST as a float value. The reason I can skip this step is that I understand the data in the table and know there won\u2019t be an issue. Normally you would need to check to make sure all the values in the field can be CAST to the specified data type. Because of what I mentioned in the previous item, the database engine is going to CAST the entire column at the same time. If the T-SQL database engine has a field value it can\u2019t CAST, then a run time error would be generated. An example of this would be if one of the rows had a value of \u2018AXT12345\u2019. T-SQL cannot CAST alphanumeric values to a float value, and you will receive the error message \u201cError converting data type varchar to float.\u201d<\/strong><\/p>\n
Hopefully this helps explains the CAST function and spurs some thoughts on using of the CAST function. If you have specific examples, tips, tricks that utilize the CAST function, let me know.<\/p>\n
Happy Mining
\n–Michael<\/p>\n
Extra Info<\/p>\n
Below is a chart that shows which data types can be CAST to other data types.<\/p>\n
\"T-SQL
T-SQL Cast Table from microsoft.com<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
\u201cMaking a square peg fit into a round hole\u201d There are many times when dealing with data that you need to transfigure one data set to allow for comparison purposes. SQL has built in functions that easily allow us to transfigure data. There are some considerations when using the functions, most importantly understanding what will […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[43,4,6],"tags":[13,14,15,17],"series":[],"class_list":["post-82","post","type-post","status-publish","format-standard","hentry","category-sql-t-sql","category-code","category-sql","tag-hint","tag-sql","tag-t-sql","tag-cast"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/82","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=82"}],"version-history":[{"count":4,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/82\/revisions"}],"predecessor-version":[{"id":142,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/82\/revisions\/142"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=82"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=82"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=82"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=82"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}