{"id":1673,"date":"2024-03-07T18:26:55","date_gmt":"2024-03-07T17:26:55","guid":{"rendered":"https:\/\/sql.marcus-belz.de\/?p=1673"},"modified":"2024-03-10T11:32:32","modified_gmt":"2024-03-10T10:32:32","slug":"data-quality-converting-data-to-bit","status":"publish","type":"post","link":"https:\/\/sql.marcus-belz.de\/?p=1673","title":{"rendered":"TRY_CONVERT \/\/ Converting data to bit"},"content":{"rendered":"\n<h1 class=\"wp-block-heading\">Content<\/h1>\n\n\n\n<ul>\n<li><a href=\"#AnchorOverview\">Overview<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorfnConvertBit\">[dbo].[fnConvertBit]<\/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=\"AnchorOverview\">Overview<\/h1>\n\n\n\n<p><em>SQL Server<\/em> provides the bit data type for storing yes\/no information. Converting an input value into a value of type <em>bit <\/em>can be a small challenge. Ideally, yes\/no information is stored in the source system in a data type that only allows a 0 or 1. In fact, yes\/no information is sometimes stored as text in strange ways. You can find the still understandable texts <em>Y<\/em> and <em>N<\/em> for <em>Yes<\/em> and <em>No<\/em> or <em>J<\/em> and <em>N<\/em> for <em>Ja<\/em> and <em>No<\/em> (German). But you can also find <em>ON<\/em> and <em>OFF<\/em>, an <em>X<\/em> or a minus sign for <em>No<\/em> and much more.<\/p>\n\n\n\n<p><em>SQL Server<\/em> cannot provide a universally valid conversion function for the numerous variants of storing yes\/no information. The developer must write a user-defined function for this that performs the conversion and returns a <em>NULL <\/em>in the event of an error &#8211; analogous to 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. This article introduces such a function <strong>[dbo].[fnConvertBit].<\/strong> Expected values are hard-coded in this function and translated into a <em>0<\/em> or <em>1<\/em>. A <em>NULL <\/em>is returned for unknown values. This function may need to be adapted to the values actually supplied. It is not necessary to differentiate between the data type in which an input value is transferred. The parameter <strong>p_Value <\/strong>is of type <em>nvarchar<\/em>. If values of type <em>No Text <\/em>are passed, <em>SQL Server<\/em> implicitly converts the transferred value into a value of type <em>nvarchar<\/em>.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorfnConvertBit\">[dbo].[fnConvertBit]<\/h1>\n\n\n\n<p>Converts a transferred input value into the target data type <em>bit<\/em>. If the input value cannot be converted, <em>NULL<\/em> is returned. The transferred value is treated as case insensitive.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code>&#91;dbo].&#91;fnConvertBit](@p_value AS nvarchar(5))<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Arguments<\/h2>\n\n\n\n<ul>\n<li><strong>p_value<br><\/strong>Indicates the input value to be converted.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Return<\/h2>\n\n\n\n<p>Returns the converted value as a value of type <em>bit <\/em>if the conversion is successful. If the input value cannot be converted, <em>NULL <\/em>is returned.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Supported input values<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><strong>True<\/strong><strong><\/strong><\/td><td><strong>False<\/strong><strong><\/strong><\/td><\/tr><tr><td>&#8216;J&#8217;<\/td><td>&#8216;N&#8217;<\/td><\/tr><tr><td>&#8216;JA&#8217;<\/td><td>&#8216;NEIN&#8217;<\/td><\/tr><tr><td>&#8216;Y&#8217;<\/td><td>&#8216;N&#8217;<\/td><\/tr><tr><td>&#8216;YES&#8217;<\/td><td>&#8216;NO&#8217;<\/td><\/tr><tr><td>&#8216;1&#8217;, &#8216;-1&#8217;<\/td><td>&#8216;0&#8217;<\/td><\/tr><tr><td>&#8216;ON&#8217;<\/td><td>&#8216;OFF&#8217;<\/td><\/tr><tr><td>&#8216;TRUE&#8217;<\/td><td>&#8216;FALSE&#8217;<\/td><\/tr><tr><td>&#8216;x&#8217;<\/td><td>&#8216;-&#8216;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Code<\/h2>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code>&nbsp;1: CREATE FUNCTION &#91;dbo].&#91;fnConvertBit] (@p_value AS nvarchar(5)) \n 2: RETURNS bit \n 3: AS \n 4: BEGIN \n 5:    -- -------------------------------------------------------------------------------- \n 6:    -- Declare variables \n 7:    -- -------------------------------------------------------------------------------- \n 8:    DECLARE @returnValue       AS bit; \n 9:  \n10:    -- -------------------------------------------------------------------------------- \n11:    -- Workload \n12:    -- -------------------------------------------------------------------------------- \n13:    SET @p_value = UPPER(TRIM(@p_value)); \n14:    SET @returnValue = CASE @p_value \n15:                            WHEN N'J'     THEN 1 \n16:                            WHEN N'JA'    THEN 1 \n17:                            WHEN N'Y'     THEN 1 \n18:                            WHEN N'YES'   THEN 1 \n19:                            WHEN N'N'     THEN 0 \n20:                            WHEN N'NEIN'  THEN 0 \n21:                            WHEN N'NO'    THEN 0 \n22:                            WHEN N'TRUE'  THEN 1 \n23:                            WHEN N'FALSE' THEN 0 \n24:                            WHEN N'ON'    THEN 1 \n25:                            WHEN N'OFF'   THEN 0 \n26:                            WHEN N'-1'    THEN 1 \n27:                            WHEN N'1'     THEN 1 \n28:                            WHEN N'0'     THEN 0 \n29:                            WHEN N'-'     THEN 0 \n30:                            ELSE NULL \n31:                         END; \n32:  \n33:    RETURN @returnValue; \n34: END;  <\/code><\/pre>\n\n\n\n<p>Und hier einige Beispiele&#8230;<\/p>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code> 1: SELECT &#91;dbo].&#91;fnConvertBit](N'1');     -- 1\n 2: SELECT &#91;dbo].&#91;fnConvertBit](N'0');     -- 0\n 3: SELECT &#91;dbo].&#91;fnConvertBit](N'J');     -- 1\n 4: SELECT &#91;dbo].&#91;fnConvertBit](N'ja');    -- 1\n 5: SELECT &#91;dbo].&#91;fnConvertBit](N'N');     -- 0\n 6: SELECT &#91;dbo].&#91;fnConvertBit](N'nein');  -- 0\n 7: SELECT &#91;dbo].&#91;fnConvertBit](N' ');     -- NULL\n 8: SELECT &#91;dbo].&#91;fnConvertBit](N'X');     -- NULL\n 9: SELECT &#91;dbo].&#91;fnConvertBit](NULL);     -- NULL\n10: SELECT &#91;dbo].&#91;fnConvertBit](N'true');  -- 1\n11: SELECT &#91;dbo].&#91;fnConvertBit](N'false'); -- 0\n12: SELECT &#91;dbo].&#91;fnConvertBit](N'ON');    -- 1\n13: SELECT &#91;dbo].&#91;fnConvertBit](N'OFF');   -- 0\n14: SELECT &#91;dbo].&#91;fnConvertBit](N'-');     -- 0\n15: SELECT &#91;dbo].&#91;fnConvertBit](NULL);     -- 1<\/code><\/pre>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorRelatedPosts\">Related Posts<\/h1>\n\n\n\n<ul>\n<li><a href=\"https:\/\/sql.marcus-belz.de\/?p=1594\">Data quality in an ETL process<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/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:\/\/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:\/\/sql.marcus-belz.de\/?p=1564\">TRY_CONVERT \/\/ Converting data to decimal or numeric<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/sql.marcus-belz.de\/?p=1607\">TRY_CONVERT \/\/ Converting data to money, smallmoney<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/sql.marcus-belz.de\/?p=1651\">TRY_CONVERT \/\/ Converting data to float, real<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/sql.marcus-belz.de\/?p=1673\">TRY_CONVERT \/\/ Converting data to bit<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/sql.marcus-belz.de\/?p=1733\">TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Content Overview SQL Server provides the bit data type for storing yes\/no information. Converting an input value into a value of type bit can be a small challenge. Ideally, yes\/no information is stored in the source system in a data <a href=\"https:\/\/sql.marcus-belz.de\/?p=1673\" class=\"read-more\">Read More &#8230;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,6],"tags":[37,47,39,35],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.2 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>TRY_CONVERT \/\/ Converting data to bit - 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 type bit analogous 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:\/\/sql.marcus-belz.de\/?p=1673\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"TRY_CONVERT \/\/ Converting data to bit - 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 type bit analogous to the TRY_CONVERT function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sql.marcus-belz.de\/?p=1673\" \/>\n<meta property=\"og:site_name\" content=\"Just another SQL blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-07T17:26:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-10T10:32:32+00:00\" \/>\n<meta name=\"author\" content=\"marcus\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"marcus\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/sql.marcus-belz.de\/?p=1673\",\"url\":\"https:\/\/sql.marcus-belz.de\/?p=1673\",\"name\":\"TRY_CONVERT \/\/ Converting data to bit - Just another SQL blog\",\"isPartOf\":{\"@id\":\"https:\/\/sql.marcus-belz.de\/#website\"},\"datePublished\":\"2024-03-07T17:26:55+00:00\",\"dateModified\":\"2024-03-10T10:32:32+00:00\",\"author\":{\"@id\":\"https:\/\/sql.marcus-belz.de\/#\/schema\/person\/7b46a383907dc48ca44fae32ceb24744\"},\"description\":\"This article describes the safe ype conversion in SQL Server of texts into the data type bit analogous to the TRY_CONVERT function.\",\"breadcrumb\":{\"@id\":\"https:\/\/sql.marcus-belz.de\/?p=1673#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/sql.marcus-belz.de\/?p=1673\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/sql.marcus-belz.de\/?p=1673#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/sql.marcus-belz.de\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"TRY_CONVERT \/\/ Converting data to bit\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/sql.marcus-belz.de\/#website\",\"url\":\"https:\/\/sql.marcus-belz.de\/\",\"name\":\"Just another SQL blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/sql.marcus-belz.de\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/sql.marcus-belz.de\/#\/schema\/person\/7b46a383907dc48ca44fae32ceb24744\",\"name\":\"marcus\",\"url\":\"https:\/\/sql.marcus-belz.de\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"TRY_CONVERT \/\/ Converting data to bit - Just another SQL blog","description":"This article describes the safe ype conversion in SQL Server of texts into the data type bit analogous 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:\/\/sql.marcus-belz.de\/?p=1673","og_locale":"en_US","og_type":"article","og_title":"TRY_CONVERT \/\/ Converting data to bit - Just another SQL blog","og_description":"This article describes the safe ype conversion in SQL Server of texts into the data type bit analogous to the TRY_CONVERT function.","og_url":"https:\/\/sql.marcus-belz.de\/?p=1673","og_site_name":"Just another SQL blog","article_published_time":"2024-03-07T17:26:55+00:00","article_modified_time":"2024-03-10T10:32:32+00:00","author":"marcus","twitter_card":"summary_large_image","twitter_misc":{"Written by":"marcus","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/sql.marcus-belz.de\/?p=1673","url":"https:\/\/sql.marcus-belz.de\/?p=1673","name":"TRY_CONVERT \/\/ Converting data to bit - Just another SQL blog","isPartOf":{"@id":"https:\/\/sql.marcus-belz.de\/#website"},"datePublished":"2024-03-07T17:26:55+00:00","dateModified":"2024-03-10T10:32:32+00:00","author":{"@id":"https:\/\/sql.marcus-belz.de\/#\/schema\/person\/7b46a383907dc48ca44fae32ceb24744"},"description":"This article describes the safe ype conversion in SQL Server of texts into the data type bit analogous to the TRY_CONVERT function.","breadcrumb":{"@id":"https:\/\/sql.marcus-belz.de\/?p=1673#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sql.marcus-belz.de\/?p=1673"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sql.marcus-belz.de\/?p=1673#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sql.marcus-belz.de\/"},{"@type":"ListItem","position":2,"name":"TRY_CONVERT \/\/ Converting data to bit"}]},{"@type":"WebSite","@id":"https:\/\/sql.marcus-belz.de\/#website","url":"https:\/\/sql.marcus-belz.de\/","name":"Just another SQL blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sql.marcus-belz.de\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sql.marcus-belz.de\/#\/schema\/person\/7b46a383907dc48ca44fae32ceb24744","name":"marcus","url":"https:\/\/sql.marcus-belz.de\/?author=1"}]}},"_links":{"self":[{"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/1673"}],"collection":[{"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1673"}],"version-history":[{"count":8,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/1673\/revisions"}],"predecessor-version":[{"id":1798,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/1673\/revisions\/1798"}],"wp:attachment":[{"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1673"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}