Data quality in SQL Server // TRY_CONVERT for bit done safely — converting yes/no values

Anyone who has ever taken over a yes/no column from a legacy export knows the pattern: the source delivers 'J', 'ON', or a plain 'x', and TRY_CONVERT(bit, N'J') answers the German notation with NULL. Out of the box, SQL Server understands only integer strings and the literals 'true'/'false' at the bit target — every other yes/no notation needs an explicit mapping.

At a glance:

• A bit stores a yes/no value: 0, 1, and NULL as a third state (three-valued logic). SQL Server stores bit columns space-efficiently and packs up to 8 bit columns of a row into a single byte.
• TRY_CONVERT(bit, …) understands integer strings under the rule “anything but 0 yields 1” and the literals 'true'/'false' — case-insensitive and locale-independent. The silent trap: an empty string converts to 0, not NULL (measured on SQL Server 2019 and 2022, not settled by the documentation).
• The dedicated function [dbo].[fn_convert_bit] maps yes/no notations ('J', 'JA', 'ON', 'x', …) to 0/1 through an explicit allow-list (CASE). Unknown values and the empty string deliberately stay NULL.
• Postgres key finding: boolean is a native type there, and the cast s::boolean accepts more yes/no forms out of the box than SQL Server. On invalid input, however, it raises an exception — the try behavior only comes with the wrapper fn_try_cast_boolean.

Prerequisite: TRY_CONVERT has existed since SQL Server 2012. The fn_convert_bit function uses TRIM and therefore needs SQL Server 2017 or newer, before that LTRIM(RTRIM(…)). All examples run without a sample database. The Postgres examples do not require a specific version, with one exception: the pg_input_is_valid pre-check mentioned below arrived with Postgres 16.

Content

• The bit data type — value range and storage
• Converting text to bit
• Converting typed numbers to bit
• Safe type conversion
• [dbo].[fn_convert_bit] — a dedicated function for non-standard notations
• Postgres bridge
• Summary
• FAQ
• Related Posts

The bit data type — value range and storage

The NULL option matters in the import context: an empty CSV value usually means “unknown” and should land as NULL, not as 0. That is exactly what the built-in converter does not deliver — it turns the empty string into a 0 (next section). Only the dedicated function further down maps it consistently to NULL.

Converting text to bit

Text input values typically come from CSV, JSON, and XML imports. When such an nvarchar/varchar value is passed to TRY_CONVERT, the bit conversion accepts two input categories: integer strings, mapped under the rule “anything but 0 yields 1”, and the literals 'true'/'false'. Surrounding spaces are allowed. Every other notation yields NULL.

  1: SELECT TRY_CONVERT(bit, NULL     )  -- NULL
  2: SELECT TRY_CONVERT(bit, N'1'     )  -- 1
  3: SELECT TRY_CONVERT(bit, N'0'     )  -- 0
  4: SELECT TRY_CONVERT(bit, N'-1'    )  -- 1
  5: SELECT TRY_CONVERT(bit, N'42'    )  -- 1
  6: SELECT TRY_CONVERT(bit, N'true'  )  -- 1
  7: SELECT TRY_CONVERT(bit, N'FALSE' )  -- 0
  8: SELECT TRY_CONVERT(bit, N' 1 '   )  -- 1
  9: SELECT TRY_CONVERT(bit, N'1.5'   )  -- NULL
 10: SELECT TRY_CONVERT(bit, N'J'     )  -- NULL
 11: SELECT TRY_CONVERT(bit, N'YES'   )  -- NULL
 12: SELECT TRY_CONVERT(bit, N'ON'    )  -- NULL
 13: SELECT TRY_CONVERT(bit, N'x'     )  -- NULL
 14: SELECT TRY_CONVERT(bit, N''      )  -- 0
 15: SELECT TRY_CONVERT(bit, N' '     )  -- 0
 16: SELECT TRY_CONVERT(bit, N'-'     )  -- 0

  1: SELECT TRY_CONVERT(bit, NULL     )  -- NULL
  2: SELECT TRY_CONVERT(bit, N'1'     )  -- 1
  3: SELECT TRY_CONVERT(bit, N'0'     )  -- 0
  4: SELECT TRY_CONVERT(bit, N'-1'    )  -- 1
  5: SELECT TRY_CONVERT(bit, N'42'    )  -- 1
  6: SELECT TRY_CONVERT(bit, N'true'  )  -- 1
  7: SELECT TRY_CONVERT(bit, N'FALSE' )  -- 0
  8: SELECT TRY_CONVERT(bit, N' 1 '   )  -- 1
  9: SELECT TRY_CONVERT(bit, N'1.5'   )  -- NULL
 10: SELECT TRY_CONVERT(bit, N'J'     )  -- NULL
 11: SELECT TRY_CONVERT(bit, N'YES'   )  -- NULL
 12: SELECT TRY_CONVERT(bit, N'ON'    )  -- NULL
 13: SELECT TRY_CONVERT(bit, N'x'     )  -- NULL
 14: SELECT TRY_CONVERT(bit, N''      )  -- 0
 15: SELECT TRY_CONVERT(bit, N' '     )  -- 0
 16: SELECT TRY_CONVERT(bit, N'-'     )  -- 0

Line by line:

• Lines 2–5: Integer strings follow the rule “anything but 0 yields 1”. '-1' and '42' also become 1. That is documented semantics: every non-zero value converts to 1 at the bit target, and there is no range check.
• Lines 6–7: The literals 'true'/'false' are case-insensitive — and that holds even under a case-sensitive or binary collation (measured with COLLATE Latin1_General_CS_AS and Latin1_General_BIN2). The conversion is also locale-independent: SET LANGUAGE changes none of the results in this block.
• Line 8: Surrounding spaces are allowed. That applies to the plain space character only — a tab in the same position yielded NULL in the measurement.
• Line 9: Decimal representations as text fail, including an innocent-looking '1.0'. Only integer representations are accepted.
• Lines 10–13: Yes/no notations are not recognized. German 'J', English 'YES', the switch notation 'ON', and the legacy marker 'x' all fall back to NULL.
• Lines 14–16: The empty string, a string of nothing but spaces, and a lone sign character ('-', '+') convert to 0. That is measured behavior (SQL Server 2019 and 2022, identical for CAST, TRY_CAST, and TRY_CONVERT) — the Microsoft documentation does not settle this case. The integer targets show the same result: TRY_CONVERT(int, N'') also yields 0, see Data quality in SQL Server // TRY_CONVERT for bigint, int, smallint and tinyint done safely.

Key point: An empty field in an import semantically means “unknown”, but TRY_CONVERT(bit, …) turns it into the business value 0 — silently, without an error, without a NULL. A '0' from the source and an empty field are no longer distinguishable in the target afterwards. Where that is wrong (the norm for CSV imports), the pattern from Safe type conversion maps the empty string to NULL up front.

Converting typed numbers to bit

If the input value arrives already typed, say as an integer, decimal, float, or money, the behavior reduces to a single rule: 0 becomes 0, every other value becomes 1.

  1: SELECT TRY_CONVERT(bit, 1)                    -- 1
  2: SELECT TRY_CONVERT(bit, 0)                    -- 0
  3: SELECT TRY_CONVERT(bit, -1)                   -- 1
  4: SELECT TRY_CONVERT(bit, 42)                   -- 1
  5: SELECT TRY_CONVERT(bit, 0.5)                  -- 1
  6: SELECT TRY_CONVERT(bit, 123456E-3)            -- 1
  7: SELECT TRY_CONVERT(bit, CAST(0.50 AS money))  -- 1
  8: SELECT TRY_CONVERT(bit, CAST(0.00 AS money))  -- 0

  1: SELECT TRY_CONVERT(bit, 1)                    -- 1
  2: SELECT TRY_CONVERT(bit, 0)                    -- 0
  3: SELECT TRY_CONVERT(bit, -1)                   -- 1
  4: SELECT TRY_CONVERT(bit, 42)                   -- 1
  5: SELECT TRY_CONVERT(bit, 0.5)                  -- 1
  6: SELECT TRY_CONVERT(bit, 123456E-3)            -- 1
  7: SELECT TRY_CONVERT(bit, CAST(0.50 AS money))  -- 1
  8: SELECT TRY_CONVERT(bit, CAST(0.00 AS money))  -- 0

Line by line:

• Lines 3–4: Sign and magnitude play no role — -1 and 42 equally become 1. There is no range check, and even the bigint maximum value becomes 1.
• Lines 5–6: Decimal numbers and float literals in scientific notation are neither rounded nor truncated. The “anything but 0” rule applies before any digit consideration: 0.5 becomes 1.
• Lines 7–8: money follows the rule as well — only an exact 0 yields 0.

Key point: Converting typed numbers to bit knows neither rounding nor truncation nor a range limit, only the documented mapping “0 stays 0, everything else becomes 1“. The result can defy integer intuition: TRY_CONVERT(int, 0.5) truncates to 0, TRY_CONVERT(bit, 0.5) yields 1. Strict 0/1 validation is not something the conversion delivers either — 42 silently becomes 1, with no hint about the unexpected input. If the business rule is “only exactly 0 and 1 are valid” or a threshold is intended (“yes from x upward”), that check belongs in the query as explicit comparison logic, not in the type conversion.

Safe type conversion

“Safe” in this series means fault-tolerant: no abort, NULL instead of a runtime error. TRY_CONVERT(bit, …) brings that property out of the box. Semantically correct the result is not yet: TRY_CONVERT answers the question of whether SQL Server can convert a value — not the question of whether the value is valid for the business. Two special cases from the sections above therefore need explicit handling in the import path:

• Empty string → 0: An empty CSV field usually stands for “unknown” and belongs in the target as NULL, not as a business “no”. The empty string therefore has to be mapped to NULL before the TRY_CONVERT — the same pattern as for the integer types.
• Unknown notation → NULL: 'J', 'YES', or 'x' silently fall back to NULL. Three different situations thereby merge into the same target NULL: the source delivered NULL, the source delivered an empty field (after the upfront mapping), or the notation is unknown. A NULL in the target is therefore not a sufficient error diagnosis. If the cases need to be distinguished, store the raw value and the converted value separately or add a dedicated conversion-status column (cross-check: see FAQ).

The application example solves the first special case:

  1: DECLARE @p_input AS nvarchar(30);
  2: SET @p_input = N'';
  3: 
  4: SELECT TRY_CONVERT( bit
  5:                   , CASE WHEN TRIM(@p_input) = '' THEN NULL ELSE @p_input END
  6:                   ) AS [Output];  -- NULL instead of 0

  1: DECLARE @p_input AS nvarchar(30);
  2: SET @p_input = N'';
  3: 
  4: SELECT TRY_CONVERT( bit
  5:                   , CASE WHEN TRIM(@p_input) = '' THEN NULL ELSE @p_input END
  6:                   ) AS [Output];  -- NULL instead of 0

Two limitations are part of the pattern:

• Input grammar: By default, TRIM removes only the plain space character (char(32)). The pattern does not recognize tabs or other Unicode whitespace as “empty”, and the bit conversion did not accept them in the measurement either — such values end up as NULL. If they can occur in the source data, normalization has to handle them explicitly.
• The notation gap remains: The pattern only fixes the empty-string case. Yes/no notations beyond integer strings and 'true'/'false' need their own mapping — the next section.

That mapping is exactly what [dbo].[fn_convert_bit] provides, and it deliberately treats the empty string as a NULL case: after the TRIM, it falls through the CASE list to ELSE NULL — that is contract logic of the function, not behavior of the SQL Server parser. How the pattern embeds into an ETL process with materialization and error identification is shown in Design Pattern // Safe Type Conversion with T-SQL.

[dbo].[fn_convert_bit] — a dedicated function for non-standard notations

ETL pipelines encounter yes/no notations from legacy sources that TRY_CONVERT(bit, …) does not map: German 'J'/'JA'/'N'/'NEIN', English 'Y'/'YES'/'NO', switch notations 'ON'/'OFF', plus markers from some legacy data sets in which 'x' stands for “set” and '-' for “not set”. The hyphen in particular is source-specific: in other data sets, '-' means “missing” or “not applicable” — then it belongs on NULL, not on 0. The following function maps the notations of its source deterministically to 0/1 through an explicit allow-list and returns NULL for unknown values.

Description

Converts a supplied input value to the target data type bit. If the input value cannot be mapped, NULL is returned. The supplied value is normalized with UPPER before the comparison. The intended ASCII notations are thereby case-insensitive, while for non-ASCII characters UPPER depends on the collation. Leading and trailing spaces are ignored (TRIM). An empty string falls through the CASE list after the TRIM and becomes NULL — unlike the direct TRY_CONVERT, which yields 0.

Syntax

  1: [dbo].[fn_convert_bit](@p_value AS nvarchar(50))

  1: [dbo].[fn_convert_bit](@p_value AS nvarchar(50))

Arguments

• p_value — the input value to be converted. The parameter type is nvarchar(50) rather than the tighter nvarchar(5) of the first draft. Longer inputs would otherwise be silently cut off at the parameter before TRIM kicks in: an nvarchar(5) parameter turns ' FALSE' (6 characters) into ' FALS', which the CASE list no longer recognizes. For a concrete source, the length follows the source schema.

Return

Returns the converted value as bit if the conversion succeeds. If the input value cannot be mapped, NULL is returned.

Supported input values

For the ASCII notations used here, the function works case-insensitively: UPPER normalizes the input before the CASE comparison, so ja, Ja, and JA land on the same mapping. For notations outside the ASCII range, UPPER depends on the collation: under a Turkish collation, 'nein' becomes 'NEİN' with a dotted İ, which no longer matches the list. The table is the function’s allow-list — it is only ever complete with respect to a concrete source, a universally valid list of all yes/no notations does not exist. The function is therefore meant as a starting skeleton. Per data source it is adapted to the notations actually delivered (e.g. 'wahr'/'falsch', 'sí'/'no').

Code

The full function definition:

  1: CREATE FUNCTION [dbo].[fn_convert_bit] (@p_value AS nvarchar(50))
  2: RETURNS bit
  3: AS
  4: BEGIN
  5:    DECLARE @return_value AS bit;
  6: 
  7:    SET @p_value      = UPPER(TRIM(@p_value));
  8:    SET @return_value = CASE @p_value
  9:                             WHEN N'J'     THEN 1
 10:                             WHEN N'JA'    THEN 1
 11:                             WHEN N'Y'     THEN 1
 12:                             WHEN N'YES'   THEN 1
 13:                             WHEN N'N'     THEN 0
 14:                             WHEN N'NEIN'  THEN 0
 15:                             WHEN N'NO'    THEN 0
 16:                             WHEN N'TRUE'  THEN 1
 17:                             WHEN N'FALSE' THEN 0
 18:                             WHEN N'ON'    THEN 1
 19:                             WHEN N'OFF'   THEN 0
 20:                             WHEN N'X'     THEN 1  -- source-specific
 21:                             WHEN N'-1'    THEN 1
 22:                             WHEN N'1'     THEN 1
 23:                             WHEN N'0'     THEN 0
 24:                             WHEN N'-'     THEN 0  -- source-specific
 25:                             ELSE NULL
 26:                        END;
 27: 
 28:    RETURN @return_value;
 29: END;

  1: CREATE FUNCTION [dbo].[fn_convert_bit] (@p_value AS nvarchar(50))
  2: RETURNS bit
  3: AS
  4: BEGIN
  5:    DECLARE @return_value AS bit;
  6: 
  7:    SET @p_value      = UPPER(TRIM(@p_value));
  8:    SET @return_value = CASE @p_value
  9:                             WHEN N'J'     THEN 1
 10:                             WHEN N'JA'    THEN 1
 11:                             WHEN N'Y'     THEN 1
 12:                             WHEN N'YES'   THEN 1
 13:                             WHEN N'N'     THEN 0
 14:                             WHEN N'NEIN'  THEN 0
 15:                             WHEN N'NO'    THEN 0
 16:                             WHEN N'TRUE'  THEN 1
 17:                             WHEN N'FALSE' THEN 0
 18:                             WHEN N'ON'    THEN 1
 19:                             WHEN N'OFF'   THEN 0
 20:                             WHEN N'X'     THEN 1  -- source-specific
 21:                             WHEN N'-1'    THEN 1
 22:                             WHEN N'1'     THEN 1
 23:                             WHEN N'0'     THEN 0
 24:                             WHEN N'-'     THEN 0  -- source-specific
 25:                             ELSE NULL
 26:                        END;
 27: 
 28:    RETURN @return_value;
 29: END;

Demo calls

16 calls, each with the expected result as a comment:

  1: SELECT [dbo].[fn_convert_bit](N'1');      -- 1
  2: SELECT [dbo].[fn_convert_bit](N'0');      -- 0
  3: SELECT [dbo].[fn_convert_bit](N'-1');     -- 1
  4: SELECT [dbo].[fn_convert_bit](N'J');      -- 1
  5: SELECT [dbo].[fn_convert_bit](N'ja');     -- 1    (case-insensitive via UPPER in the function body)
  6: SELECT [dbo].[fn_convert_bit](N'nein');   -- 0
  7: SELECT [dbo].[fn_convert_bit](N'x');      -- 1    (legacy marker: set)
  8: SELECT [dbo].[fn_convert_bit](N'-');      -- 0    (legacy marker: not set)
  9: SELECT [dbo].[fn_convert_bit](N'true');   -- 1
 10: SELECT [dbo].[fn_convert_bit](N'false');  -- 0
 11: SELECT [dbo].[fn_convert_bit](N'ON');     -- 1
 12: SELECT [dbo].[fn_convert_bit](N'OFF');    -- 0
 13: SELECT [dbo].[fn_convert_bit](N' ja ');   -- 1    (whitespace padding, TRIM applies)
 14: SELECT [dbo].[fn_convert_bit](N'');       -- NULL (empty string; a direct TRY_CONVERT would yield 0)
 15: SELECT [dbo].[fn_convert_bit](N'?');      -- NULL (unknown input value)
 16: SELECT [dbo].[fn_convert_bit](NULL);      -- NULL (NULL input stays NULL)

  1: SELECT [dbo].[fn_convert_bit](N'1');      -- 1
  2: SELECT [dbo].[fn_convert_bit](N'0');      -- 0
  3: SELECT [dbo].[fn_convert_bit](N'-1');     -- 1
  4: SELECT [dbo].[fn_convert_bit](N'J');      -- 1
  5: SELECT [dbo].[fn_convert_bit](N'ja');     -- 1    (case-insensitive via UPPER in the function body)
  6: SELECT [dbo].[fn_convert_bit](N'nein');   -- 0
  7: SELECT [dbo].[fn_convert_bit](N'x');      -- 1    (legacy marker: set)
  8: SELECT [dbo].[fn_convert_bit](N'-');      -- 0    (legacy marker: not set)
  9: SELECT [dbo].[fn_convert_bit](N'true');   -- 1
 10: SELECT [dbo].[fn_convert_bit](N'false');  -- 0
 11: SELECT [dbo].[fn_convert_bit](N'ON');     -- 1
 12: SELECT [dbo].[fn_convert_bit](N'OFF');    -- 0
 13: SELECT [dbo].[fn_convert_bit](N' ja ');   -- 1    (whitespace padding, TRIM applies)
 14: SELECT [dbo].[fn_convert_bit](N'');       -- NULL (empty string; a direct TRY_CONVERT would yield 0)
 15: SELECT [dbo].[fn_convert_bit](N'?');      -- NULL (unknown input value)
 16: SELECT [dbo].[fn_convert_bit](NULL);      -- NULL (NULL input stays NULL)

Postgres bridge

In Postgres the situation is more comfortable: boolean is a native data type, no bit substitute is needed. The direct cast s::boolean understands a considerably broader yes/no list out of the box than SQL Server’s bit parser:

  1: SELECT 't'::boolean;         -- true
  2: SELECT 'true'::boolean;      -- true
  3: SELECT 'TRUE'::boolean;      -- true   (case-insensitive)
  4: SELECT '  true  '::boolean;  -- true   (surrounding whitespace is removed)
  5: SELECT 'tr'::boolean;        -- true   (unambiguous prefix of true)
  6: SELECT 'y'::boolean;         -- true
  7: SELECT 'yes'::boolean;       -- true
  8: SELECT 'on'::boolean;        -- true
  9: SELECT '1'::boolean;         -- true
 10: 
 11: SELECT 'f'::boolean;         -- false
 12: SELECT 'n'::boolean;         -- false
 13: SELECT 'no'::boolean;        -- false
 14: SELECT 'off'::boolean;       -- false
 15: SELECT '0'::boolean;         -- false

  1: SELECT 't'::boolean;         -- true
  2: SELECT 'true'::boolean;      -- true
  3: SELECT 'TRUE'::boolean;      -- true   (case-insensitive)
  4: SELECT '  true  '::boolean;  -- true   (surrounding whitespace is removed)
  5: SELECT 'tr'::boolean;        -- true   (unambiguous prefix of true)
  6: SELECT 'y'::boolean;         -- true
  7: SELECT 'yes'::boolean;       -- true
  8: SELECT 'on'::boolean;        -- true
  9: SELECT '1'::boolean;         -- true
 10: 
 11: SELECT 'f'::boolean;         -- false
 12: SELECT 'n'::boolean;         -- false
 13: SELECT 'no'::boolean;        -- false
 14: SELECT 'off'::boolean;       -- false
 15: SELECT '0'::boolean;         -- false

Three differences compared to SQL Server:

• Broader input list: 't'/'y'/'yes'/'on'/'1' and their false counterparts, case-insensitive and whitespace-tolerant. The parser even accepts unambiguous prefixes ('tr', 'ye'). Only the ambiguous 'o' is rejected, because it could be 'on' as well as 'off'.
• Only '1' and '0' as digits: The T-SQL rule “anything but 0 yields 1” does not exist — '-1'::boolean and '42'::boolean raise an exception. Numeric truth values from a SQL Server source have to be normalized to '0'/'1' before they reach Postgres.
• Exception instead of silent values: Invalid inputs such as 'J' and also the empty string abort with invalid input syntax for type boolean (SQLSTATE 22P02, error class invalid_text_representation). A built-in try counterpart does not exist, not even in Postgres 18.

The NULL-instead-of-exception behavior plus the German notations comes from a PL/pgSQL wrapper with the same CASE mapping as the T-SQL function:

  1: CREATE OR REPLACE FUNCTION fn_try_cast_boolean
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS boolean
  6: LANGUAGE plpgsql
  7: IMMUTABLE
  8: AS $function$
  9: DECLARE
 10:    l_normalized              text;
 11: BEGIN
 12: 
 13:    IF p_input IS NULL OR TRIM(p_input) = '' THEN
 14:       RETURN NULL;
 15:    END IF;
 16: 
 17:    l_normalized := UPPER(TRIM(p_input));
 18: 
 19:    RETURN CASE l_normalized
 20:              WHEN 'J'     THEN true
 21:              WHEN 'JA'    THEN true
 22:              WHEN 'Y'     THEN true
 23:              WHEN 'YES'   THEN true
 24:              WHEN 'TRUE'  THEN true
 25:              WHEN 'T'     THEN true
 26:              WHEN 'ON'    THEN true
 27:              WHEN '1'     THEN true
 28:              WHEN '-1'    THEN true
 29:              WHEN 'X'     THEN true   -- source-specific
 30:              WHEN 'N'     THEN false
 31:              WHEN 'NEIN'  THEN false
 32:              WHEN 'NO'    THEN false
 33:              WHEN 'FALSE' THEN false
 34:              WHEN 'F'     THEN false
 35:              WHEN 'OFF'   THEN false
 36:              WHEN '0'     THEN false
 37:              WHEN '-'     THEN false  -- source-specific
 38:              ELSE NULL
 39:           END;
 40: 
 41: END;
 42: $function$;
 43: 
 44: SELECT fn_try_cast_boolean('J');       -- true
 45: SELECT fn_try_cast_boolean(' ja ');    -- true
 46: SELECT fn_try_cast_boolean('x');       -- true
 47: SELECT fn_try_cast_boolean('foo');     -- NULL
 48: SELECT fn_try_cast_boolean('');        -- NULL

  1: CREATE OR REPLACE FUNCTION fn_try_cast_boolean
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS boolean
  6: LANGUAGE plpgsql
  7: IMMUTABLE
  8: AS $function$
  9: DECLARE
 10:    l_normalized              text;
 11: BEGIN
 12: 
 13:    IF p_input IS NULL OR TRIM(p_input) = '' THEN
 14:       RETURN NULL;
 15:    END IF;
 16: 
 17:    l_normalized := UPPER(TRIM(p_input));
 18: 
 19:    RETURN CASE l_normalized
 20:              WHEN 'J'     THEN true
 21:              WHEN 'JA'    THEN true
 22:              WHEN 'Y'     THEN true
 23:              WHEN 'YES'   THEN true
 24:              WHEN 'TRUE'  THEN true
 25:              WHEN 'T'     THEN true
 26:              WHEN 'ON'    THEN true
 27:              WHEN '1'     THEN true
 28:              WHEN '-1'    THEN true
 29:              WHEN 'X'     THEN true   -- source-specific
 30:              WHEN 'N'     THEN false
 31:              WHEN 'NEIN'  THEN false
 32:              WHEN 'NO'    THEN false
 33:              WHEN 'FALSE' THEN false
 34:              WHEN 'F'     THEN false
 35:              WHEN 'OFF'   THEN false
 36:              WHEN '0'     THEN false
 37:              WHEN '-'     THEN false  -- source-specific
 38:              ELSE NULL
 39:           END;
 40: 
 41: END;
 42: $function$;
 43: 
 44: SELECT fn_try_cast_boolean('J');       -- true
 45: SELECT fn_try_cast_boolean(' ja ');    -- true
 46: SELECT fn_try_cast_boolean('x');       -- true
 47: SELECT fn_try_cast_boolean('foo');     -- NULL
 48: SELECT fn_try_cast_boolean('');        -- NULL

Two traits distinguish this wrapper from its siblings in the other articles of this series. First, it needs no EXCEPTION block: the CASE mapping never throws, unknown values run into ELSE NULL. That eliminates the cost surcharge the Postgres documentation attributes to blocks with an exception handler. Second, it inherits nothing from the native parser: prefix forms like 'tr' are not on the list and yield NULL — the CASE list is the contract. If you want to stay with the native cast instead, Postgres 16 and later can pre-check set-based with pg_input_is_valid('J', 'boolean') whether a value is convertible, without triggering a failing cast. That is a pre-check pattern, not a try-cast: checking and converting happen in two separate steps against the same input.

Summary

• Conversion to bit is a two-step decision: first check whether the built-in converter covers the source (integer strings under the “anything but 0” rule plus 'true'/'false'), only then reach for a dedicated function.
• The most important special case of the built-in: empty strings, whitespace-only strings, and lone sign characters convert to 0 (measured, not documented) — an empty CSV field thereby becomes a business “no”. The safe pattern maps the empty string to NULL up front via CASE/TRIM.
• [dbo].[fn_convert_bit] handles yes/no notations ('J', 'JA', 'ON', 'x', …) through an explicit allow-list. It deliberately treats unknown values and the empty string as NULL — matching the three-valued logic (0/1/NULL) of the bit type.
• Postgres counterpart: boolean is native with a broader input list ('t', 'yes', 'on', unambiguous prefixes), but raises an exception instead of NULL on invalid input. fn_try_cast_boolean provides the try behavior and the German notations.

A decision aid for practice:

FAQ

Related Posts

ETL context:

• Data quality in an ETL process
• ETL vs. ELT — How to Tell Which Pattern You Actually Built — the macro view: ETL vs. ELT as an architecture decision, not the order of the letters.
• Design Pattern // Safe Type Conversion with T-SQL — the overarching pattern (materialization + error identification) that embeds these TRY_CONVERT building blocks.

TRY_CONVERT for other data types:

• Data quality in SQL Server // TRY_CONVERT for date, datetime, datetime2 and time done safely
• Data quality in SQL Server // TRY_CONVERT for decimal and numeric done safely
• Data quality in SQL Server // TRY_CONVERT for bigint, int, smallint and tinyint done safely
• Data quality in SQL Server // TRY_CONVERT for money and smallmoney done safely
• Data quality in SQL Server // TRY_CONVERT for float and real done safely

Fundamentals:

• Data Quality // Type Conversion Basics with T-SQL — CAST, CONVERT, TRY_CAST and TRY_CONVERT compared.