{"id":1564,"date":"2024-03-01T21:41:32","date_gmt":"2024-03-01T20:41:32","guid":{"rendered":"https:\/\/staging.sql.marcus-belz.de\/?p=1564"},"modified":"2026-05-07T01:06:38","modified_gmt":"2026-05-06T23:06:38","slug":"data-quality-converting-data-to-decimal-or-numeric","status":"publish","type":"post","link":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en","title":{"rendered":"TRY_CONVERT \/\/ Converting data to decimal, numeric"},"content":{"rendered":"\n<p>This article describes how to safely convert input values to <em>bigint<\/em>, <em>int<\/em>, <em>smallint<\/em> and <em>tinyint <\/em>data types. A seemingly simple task, you might think. For example, what happens if you want to convert a decimal number or an empty string to a value of type <em>int<\/em>. Is the converted value automatically rounded? Does function return a NULL on conversion?<\/p>\n\n\n\n<p>The online Microsoft online documentation of the <a href=\"https:\/\/learn.microsoft.com\/de-de\/sql\/t-sql\/functions\/try-convert-transact-sql?view=sql-server-ver16\"><strong>TRY_CONVERT<\/strong> <\/a>function (or rather <strong><a href=\"https:\/\/learn.microsoft.com\/de-de\/sql\/t-sql\/functions\/cast-and-convert-transact-sql?view=sql-server-ver16\">CAST and CONVERT<\/a><\/strong>) states that the value to be converted can be any expression. This means that function does accept values of any data type: text and non text data types.<\/p>\n\n\n\n<p>When examining how to do safe type conversion, this article only considers meaningful data types that we would actually convert to a bigint, int, smallint, or tinyint value. In addition to whole numbers and decimal numbers, these also include texts, that represent either whole numbers or decimal numbers. The latter is always the case when, for example, values from a text file (CSV, JSON or XML) need to be converted.<\/p>\n\n\n\n<p>For convenience, the article distinguishes between the Text and Non-Text data types and examines the behavior of the TRY_CONVERT function when converting values of the two common data types. For the general data type No Text, only data types are taken into account that we would also convert to a value of type int if necessary. An expression for safe type conversion is presented for both general data types.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Content<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"#AnchorText\">Text<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorNoText\">No Text<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorSaveTypeConversion\">Safe type convertierung<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorSummary\">Summary<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorRelatedPosts\">Related Posts<\/a><\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorText\">Text<\/h1>\n\n\n\n<p>If we want to convert a text that is supposed to represent a number to a value of type <em>int<\/em>, the following scenarios should be examined, among others:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The text contains a valid number<\/li>\n\n\n\n<li>The number contains a comma as a decimal separator<\/li>\n\n\n\n<li>The text contains thousands separators and, if necessary, decimal separators<\/li>\n\n\n\n<li>The text is an empty string or contains only spaces<\/li>\n<\/ul>\n\n\n\n<p>The following shows calls to the <strong>TRY_CONVERT <\/strong>function that convert values as described above to a value of type <em>int<\/em>. The returned result is noted after the respective function calls:<\/p>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code> 1: SELECT TRY_CONVERT(int, NULL    ) -- &gt; NULL\n 2: SELECT TRY_CONVERT(int, N'123'  ) -- &gt; 123\n 3: SELECT TRY_CONVERT(int, N'123,4') -- &gt; NULL\n 4: SELECT TRY_CONVERT(int, N'1,234') -- &gt; NULL\n 5: SELECT TRY_CONVERT(int, N'123.4') -- &gt; NULL\n 6: SELECT TRY_CONVERT(int, N'1.234') -- &gt; NULL\n 7: SELECT TRY_CONVERT(int, N''     ) -- &gt; 0\n 8: SELECT TRY_CONVERT(int, N' '    ) -- &gt; 0\n 9: SELECT TRY_CONVERT(int, N' 123' ) -- &gt; 123\n10: SELECT TRY_CONVERT(int, N'123 ' ) -- &gt; 123<\/code><\/pre>\n\n\n\n<p>Numbers that obviously represent an integer can be converted to a number of type int, as expected. Leading and trailing spaces do not affect the result. Decimal numbers that contain a decimal separator cannot be converted to a number of type int. It is irrelevant whether the value contains a comma (German notation) or a period as a decimal separator (American notation). Empty strings or strings containing only spaces are converted to the value 0.<\/p>\n\n\n\n<p>The result already has a few surprises in store. Decimal numbers cannot be converted and empty strings result in the value 0. While the first result is understandable, the second result is simply wrong. A <em>NULL <\/em>cannot be delivered in a text file. Whether an empty string should be interpreted as <em>NULL <\/em>is a question of the data source specification. Interpreting an empty string as 0 is technically incorrect.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorNoText\">No Text<\/h1>\n\n\n\n<p>If we want to convert values that are not passed as text to the <strong>TRY_CONVERT <\/strong>function, all we need to examine are the following scenarios:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The value passed is an integer<\/li>\n\n\n\n<li>The value passed is a decimal separator<\/li>\n<\/ul>\n\n\n\n<p>The following shows calls to the <strong>TRY_CONVERT <\/strong>function that convert values as described above to a value of type <em>int<\/em>. The returned result is noted after the respective instructions:<\/p>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code> 1: SELECT TRY_CONVERT(int, 2147483648)    -- &gt; NULL\n 2: SELECT TRY_CONVERT(int, 123       )    -- &gt; 123\n 3: SELECT TRY_CONVERT(int, 1234.5    )    -- &gt; 123<\/code><\/pre>\n\n\n\n<p>The first number is greater than the largest positive number of type <em>int<\/em>. The result therefore returns a <em>NULL<\/em> as expected. Unlike above, decimals can be converted to a value of type <em>int<\/em>. However, SQL Server does not perform rounding. The <strong>TRY_CONVERT <\/strong>function only returns the integer part of the decimal number<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorSaveTypeConversion\">Safe type conversion<\/h1>\n\n\n\n<p>The two code examples have shown that at least the conversion of empty strings requires special consideration when conversion to the value 0 is not allowed. Converting a typed decimal number can also give rise to explicit treatment when rounding is required. Alternatively, a conversion to a decimal number with 0 decimal places may be the correct procedure here.<\/p>\n\n\n\n<p>The following code block shows safe type conversion considering converting empty strings to a NULL.<\/p>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code> 1: TRY_CONVERT( &#91;bigint|int|smallint|tinyint]\n 2:             ,CASE WHEN TRIM(&#91;Input]) = ''\n 3:                 THEN NULL\n 4:                 ELSE &#91;Input]\n 5:              END\n 6:            ) AS &#91;Output]<\/code><\/pre>\n\n\n\n<p>And here is an example of the application\u2026<\/p>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code> 1: DECLARE @input AS nvarchar(30);\n 2: SET @input = '123';\n 3: SELECT TRY_CONVERT(int, \n                       CASE WHEN TRIM(@input) = '' THEN NULL ELSE @input END\n                      ) AS &#91;Output]<\/code><\/pre>\n\n\n\n<p>This example converts an empty string to a NULL.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorSummary\">Summary<\/h1>\n\n\n\n<p>When converting a text into a value of type <em>int<\/em>, it must be clarified which data is to be supplied and how special cases are to be taken into account. An empty string is converted to a 0 by the <strong>TRY_CONVERT <\/strong>function. Decimal numbers that are available as text are converted to the integer part of the decimal number. If rounding is desired, this must be taken into account during the conversion.<\/p>\n\n\n\n<p>Typed decimal numbers are not rounded when converted to a value of type <em>int<\/em>. Here, too, it must be clarified whether this result is permissible or whether this feature must be taken into account during the conversion.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorRelatedPosts\">Related Posts<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1594\">Data quality in an ETL process<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1181\">Datenqualit\u00e4t \/\/ Grundlagen der Typ-Konvertierung mit T-SQL<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1576\">TRY_CONVERT \/\/ Converting data to&nbsp;bigint, int, smallint, tinyint<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1564\">TRY_CONVERT \/\/ Converting data to decimal or numeric<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1607\">TRY_CONVERT \/\/ Converting data to money, smallmoney<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1651\">TRY_CONVERT \/\/ Converting data to float, real<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1673\">TRY_CONVERT \/\/ Converting data to bit<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1733\">TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time<\/a><\/li>\n<\/ul>\n\n\n\n<p>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article describes how to safely convert input values to bigint, int, smallint and tinyint data types. A seemingly simple task, you might think. For example, what happens if you want to convert a decimal number or an empty string to a value of type int. Is the converted value automatically rounded? Does function return &#8230; <a title=\"TRY_CONVERT \/\/ Converting data to decimal, numeric\" class=\"read-more\" href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1564&#038;lang=en\" aria-label=\"Mehr Informationen \u00fcber TRY_CONVERT \/\/ Converting data to decimal, numeric\">Weiterlesen<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[106,108],"tags":[110,112,114,116],"class_list":["post-1564","post","type-post","status-publish","format-standard","hentry","category-all-languages-en","category-english-en","tag-data-quality-en","tag-data-type-en","tag-design-pattern-en","tag-try_convert-en"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>TRY_CONVERT \/\/ Converting data to decimal, numeric - Just another SQL blog<\/title>\n<meta name=\"description\" content=\"This article describes the safe ype conversion in SQL Server of texts into the data types decimal and numeric to the TRY_CONVERT function.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en\" \/>\n<meta property=\"og:locale\" content=\"de_DE\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"TRY_CONVERT \/\/ Converting data to decimal, numeric - Just another SQL blog\" \/>\n<meta property=\"og:description\" content=\"This article describes the safe ype conversion in SQL Server of texts into the data types decimal and numeric to the TRY_CONVERT function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/staging.sql.marcus-belz.de\/?p=1564&amp;lang=en\" \/>\n<meta property=\"og:site_name\" content=\"Just another SQL blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-01T20:41:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-05-06T23:06:38+00:00\" \/>\n<meta name=\"author\" content=\"marcus\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Geschrieben von\" \/>\n\t<meta name=\"twitter:data1\" content=\"marcus\" \/>\n\t<meta name=\"twitter:label2\" content=\"Gesch\u00e4tzte Lesezeit\" \/>\n\t<meta name=\"twitter:data2\" content=\"4\u00a0Minuten\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1564&lang=en#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1564&lang=en\"},\"author\":{\"name\":\"marcus\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#\\\/schema\\\/person\\\/98ab042e47d7286f64530ee18f20f675\"},\"headline\":\"TRY_CONVERT \\\/\\\/ Converting data to decimal, numeric\",\"datePublished\":\"2024-03-01T20:41:32+00:00\",\"dateModified\":\"2026-05-06T23:06:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1564&lang=en\"},\"wordCount\":883,\"keywords\":[\"Data Quality\",\"Data type\",\"Design Pattern\",\"TRY_CONVERT\"],\"articleSection\":[\"All Languages\",\"English\"],\"inLanguage\":\"de\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1564&lang=en\",\"url\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1564&lang=en\",\"name\":\"TRY_CONVERT \\\/\\\/ Converting data to decimal, numeric - Just another SQL blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#website\"},\"datePublished\":\"2024-03-01T20:41:32+00:00\",\"dateModified\":\"2026-05-06T23:06:38+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#\\\/schema\\\/person\\\/98ab042e47d7286f64530ee18f20f675\"},\"description\":\"This article describes the safe ype conversion in SQL Server of texts into the data types decimal and numeric to the TRY_CONVERT function.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1564&lang=en#breadcrumb\"},\"inLanguage\":\"de\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1564&lang=en\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1564&lang=en#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"TRY_CONVERT \\\/\\\/ Converting data to decimal, numeric\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#website\",\"url\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/\",\"name\":\"Just another SQL blog\",\"description\":\"Marcus Belz \u00b7 SQL Server \u00b7 ETL \u00b7 Datenqualit\u00e4t\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"de\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#\\\/schema\\\/person\\\/98ab042e47d7286f64530ee18f20f675\",\"name\":\"marcus\",\"url\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"TRY_CONVERT \/\/ Converting data to decimal, numeric - Just another SQL blog","description":"This article describes the safe ype conversion in SQL Server of texts into the data types decimal and numeric to the TRY_CONVERT function.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en","og_locale":"de_DE","og_type":"article","og_title":"TRY_CONVERT \/\/ Converting data to decimal, numeric - Just another SQL blog","og_description":"This article describes the safe ype conversion in SQL Server of texts into the data types decimal and numeric to the TRY_CONVERT function.","og_url":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en","og_site_name":"Just another SQL blog","article_published_time":"2024-03-01T20:41:32+00:00","article_modified_time":"2026-05-06T23:06:38+00:00","author":"marcus","twitter_card":"summary_large_image","twitter_misc":{"Geschrieben von":"marcus","Gesch\u00e4tzte Lesezeit":"4\u00a0Minuten"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en#article","isPartOf":{"@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en"},"author":{"name":"marcus","@id":"https:\/\/staging.sql.marcus-belz.de\/#\/schema\/person\/98ab042e47d7286f64530ee18f20f675"},"headline":"TRY_CONVERT \/\/ Converting data to decimal, numeric","datePublished":"2024-03-01T20:41:32+00:00","dateModified":"2026-05-06T23:06:38+00:00","mainEntityOfPage":{"@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en"},"wordCount":883,"keywords":["Data Quality","Data type","Design Pattern","TRY_CONVERT"],"articleSection":["All Languages","English"],"inLanguage":"de"},{"@type":"WebPage","@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en","url":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en","name":"TRY_CONVERT \/\/ Converting data to decimal, numeric - Just another SQL blog","isPartOf":{"@id":"https:\/\/staging.sql.marcus-belz.de\/#website"},"datePublished":"2024-03-01T20:41:32+00:00","dateModified":"2026-05-06T23:06:38+00:00","author":{"@id":"https:\/\/staging.sql.marcus-belz.de\/#\/schema\/person\/98ab042e47d7286f64530ee18f20f675"},"description":"This article describes the safe ype conversion in SQL Server of texts into the data types decimal and numeric to the TRY_CONVERT function.","breadcrumb":{"@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en#breadcrumb"},"inLanguage":"de","potentialAction":[{"@type":"ReadAction","target":["https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1564&lang=en#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/staging.sql.marcus-belz.de\/"},{"@type":"ListItem","position":2,"name":"TRY_CONVERT \/\/ Converting data to decimal, numeric"}]},{"@type":"WebSite","@id":"https:\/\/staging.sql.marcus-belz.de\/#website","url":"https:\/\/staging.sql.marcus-belz.de\/","name":"Just another SQL blog","description":"Marcus Belz \u00b7 SQL Server \u00b7 ETL \u00b7 Datenqualit\u00e4t","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/staging.sql.marcus-belz.de\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"de"},{"@type":"Person","@id":"https:\/\/staging.sql.marcus-belz.de\/#\/schema\/person\/98ab042e47d7286f64530ee18f20f675","name":"marcus","url":"https:\/\/staging.sql.marcus-belz.de\/?author=1"}]}},"_links":{"self":[{"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/1564","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1564"}],"version-history":[{"count":10,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/1564\/revisions"}],"predecessor-version":[{"id":1780,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/1564\/revisions\/1780"}],"wp:attachment":[{"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1564"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1564"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1564"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}