Data quality starts with type conversion — and for bit columns it shows up right at the input value: yes/no information comes out of legacy sources in a wide variety of notations ('J', 'Y', 'ON', '1', 'x', '-', …). SQL Server’s built-in TRY_CONVERT(bit, …) only covers the integer standard plus 'true'/'false' — everything else needs a dedicated conversion function. This article describes both: what’s built in, when an fn_convert_bit function is required, and what the Postgres counterpart looks like.
At a glance:
- A
bitcarries logically one bit of information (0/1, plusNULLas 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 the integer standard plus'true'/'false'— case-insensitive and locale-independent.- A dedicated function
[dbo].[fn_convert_bit]maps yes/no notations ('J'/'YES'/'ON'/'x'/'-', …) to 0/1 and returnsNULLfor unknown inputs. - Postgres bridge:
booleanas a native type,s::booleanwith built-in yes/no tolerance, plus atry_cast_booleanwrapper for non-standard notations.
Prerequisite: SQL Server 2017+ (the fn_convert_bit function below uses TRIM, which is only available from 2017 — on 2012–2016, use LTRIM(RTRIM(...)) instead). Examples run without a sample dataset — pure inline literals.
Content
- The
bitdata type — value range and storage TRY_CONVERT(bit, …)— what’s built in[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
| Property | Value |
|---|---|
| Value range | 0, 1, and NULL (three-valued logic: 0, 1, “unknown”) |
| Storage | Logically one bit of information per value. SQL Server stores BIT columns space-efficiently and packs up to 8 BIT columns of a row into 1 byte. |
vs. boolean | SQL Server has no native BOOLEAN data type for columns or variables. Boolean expressions exist only internally in predicates and conditions (IS NULL, EXISTS, …). Postgres has boolean as a native data type. |
The NULL option matters in the import context: an empty CSV value is usually “unknown” semantically and should land as NULL — not as 0. The conversion paths in the following sections therefore map empty strings to NULL consistently.
TRY_CONVERT(bit, …) — what’s built in
Before writing a dedicated function, it’s worth looking at what SQL Server already delivers. TRY_CONVERT(bit, …) understands a narrow but precise list of inputs. Processing is case-insensitive and locale-independent.
1: -- Standard-Inputs, die TRY_CONVERT(bit, ...) direkt versteht (Ergebnis: 0 oder 1):
2: SELECT TRY_CONVERT(bit, N'1'); -- 1
3: SELECT TRY_CONVERT(bit, N'0'); -- 0
4: SELECT TRY_CONVERT(bit, N'-1'); -- 1 (jeder von 0 verschiedene Integer-String > 1)
5: SELECT TRY_CONVERT(bit, N'true'); -- 1 (case-insensitiv)
6: SELECT TRY_CONVERT(bit, N'false'); -- 0
7: SELECT TRY_CONVERT(bit, 1); -- 1 (numerischer Input)
8: SELECT TRY_CONVERT(bit, 42); -- 1 (jeder von 0 verschiedene Integer > 1)
The built-in covers exactly two input categories: integer strings/numbers (with the “anything but 0 → 1” rule) and the literals 'true'/'false' (case-insensitive). For everything else the call falls back to NULL:
1: -- Non-Standard-Inputs, an denen TRY_CONVERT(bit, ...) scheitert (Ergebnis: NULL):
2: SELECT TRY_CONVERT(bit, N'J'); -- NULL
3: SELECT TRY_CONVERT(bit, N'YES'); -- NULL
4: SELECT TRY_CONVERT(bit, N'ON'); -- NULL
5: SELECT TRY_CONVERT(bit, N'x'); -- NULL
6: SELECT TRY_CONVERT(bit, N'-'); -- NULL
7: SELECT TRY_CONVERT(bit, N''); -- NULL (leere Zeichenfolge; getestet auf SQL Server 2019/2022)
8: SELECT TRY_CONVERT(bit, NULL); -- NULL
Worth noting: TRY_CONVERT(bit, N'') returns NULL directly (verified on SQL Server 2019 and 2022). TRY_CONVERT(int, N'') instead converts to the value 0 — see TRY_CONVERT // Converting data to bigint, int, smallint, tinyint. In the bit path you therefore do not need an additional CASE/TRIM pattern to catch the empty string. On older editions, verify the behavior against the concrete version — string conversions in SQL Server have historically had version-dependent quirks.
If the source delivers only '0'/'1'/'true'/'false', TRY_CONVERT is sufficient. For anything else — 'J', 'YES', 'ON', 'x', '-', … — the next section takes over.
[dbo].[fn_convert_bit] — a dedicated function for non-standard notations
In ETL pipelines you 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 historical markers like 'x' (set) and '-' (unset). The following function maps these notations deterministically to 0/1 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 treated case-insensitively (UPPER in the function body). Leading and trailing whitespace is ignored (TRIM).
Syntax
1: [dbo].[fn_convert_bit](@p_value AS nvarchar(50))
Arguments
p_value— the input value to be converted. The parameter type isnvarchar(50)(rather than the tighternvarchar(5)of an earlier draft) — this gives whitespace padding from the source some room beforeTRIMkicks in.
Return
Returns the converted value as bit if the conversion succeeds. If the input value cannot be mapped, NULL is returned.
Supported input values
Input → 1 | Input → 0 |
|---|---|
J | N |
JA | NEIN |
Y | NO |
YES | — |
TRUE | FALSE |
ON | OFF |
1, -1 | 0 |
| — | - |
Values are case-insensitive. Inside the function body, UPPER normalizes the input before the CASE comparison. The function is 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'-1' THEN 1
21: WHEN N'1' THEN 1
22: WHEN N'0' THEN 0
23: WHEN N'-' THEN 0
24: ELSE NULL
25: END;
26:
27: RETURN @return_value;
28: END;
Demo calls
15 calls, 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'J'); -- 1
4: SELECT [dbo].[fn_convert_bit](N'ja'); -- 1 (case-insensitiv über UPPER im Funktions-Body)
5: SELECT [dbo].[fn_convert_bit](N'N'); -- 0
6: SELECT [dbo].[fn_convert_bit](N'nein'); -- 0
7: SELECT [dbo].[fn_convert_bit](N' '); -- NULL (nach TRIM eine leere Zeichenfolge, nicht in CASE-Liste)
8: SELECT [dbo].[fn_convert_bit](N'X'); -- NULL (unbekannter Eingangswert)
9: SELECT [dbo].[fn_convert_bit](NULL); -- NULL (NULL-Input bleibt NULL)
10: SELECT [dbo].[fn_convert_bit](N'true'); -- 1
11: SELECT [dbo].[fn_convert_bit](N'false'); -- 0
12: SELECT [dbo].[fn_convert_bit](N'ON'); -- 1
13: SELECT [dbo].[fn_convert_bit](N'OFF'); -- 0
14: SELECT [dbo].[fn_convert_bit](N'-'); -- 0
15: SELECT [dbo].[fn_convert_bit](N'-1'); -- 1
Postgres bridge
In Postgres the situation is more comfortable: boolean is a native data type, and the direct cast s::boolean understands a broader yes/no list out of the box than SQL Server’s TRY_CONVERT(bit, …).
1: SELECT 't'::boolean; -- true
2: SELECT 'true'::boolean; -- true
3: SELECT 'y'::boolean; -- true
4: SELECT 'yes'::boolean; -- true
5: SELECT 'on'::boolean; -- true
6: SELECT '1'::boolean; -- true
7:
8: SELECT 'f'::boolean; -- false
9: SELECT 'n'::boolean; -- false
10: SELECT 'no'::boolean; -- false
11: SELECT 'off'::boolean; -- false
However: s::boolean raises an exception on invalid inputs ('J'::boolean → ERROR: invalid input syntax for type boolean: "J"). For non-standard notations — analogous to the SQL Server custom function — you need a PL/pgSQL wrapper that returns NULL instead of raising, and that covers the German 'J'/'JA'/'NEIN' plus 'x'/'-' as well:
1: CREATE OR REPLACE FUNCTION try_cast_boolean (p_value text)
2: RETURNS boolean
3: LANGUAGE plpgsql
4: IMMUTABLE
5: AS $$
6: DECLARE
7: normalized text;
8: BEGIN
9: IF p_value IS NULL THEN
10: RETURN NULL;
11: END IF;
12: normalized := UPPER(TRIM(p_value));
13: IF normalized = '' THEN
14: RETURN NULL;
15: END IF;
16: RETURN CASE normalized
17: WHEN 'J' THEN true
18: WHEN 'JA' THEN true
19: WHEN 'Y' THEN true
20: WHEN 'YES' THEN true
21: WHEN 'TRUE' THEN true
22: WHEN 'T' THEN true
23: WHEN 'ON' THEN true
24: WHEN '1' THEN true
25: WHEN '-1' THEN true
26: WHEN 'N' THEN false
27: WHEN 'NEIN' THEN false
28: WHEN 'NO' THEN false
29: WHEN 'FALSE' THEN false
30: WHEN 'F' THEN false
31: WHEN 'OFF' THEN false
32: WHEN '0' THEN false
33: WHEN '-' THEN false
34: ELSE NULL
35: END;
36: END;
37: $$;
| SQL Server | Postgres counterpart | Note |
|---|---|---|
TRY_CONVERT(bit, '1') | '1'::boolean or try_cast_boolean('1') | identical built-in |
TRY_CONVERT(bit, 'true') | 'true'::boolean or try_cast_boolean('true') | identical built-in |
TRY_CONVERT(bit, 'J') → NULL | 'J'::boolean → exception; try_cast_boolean('J') → true | wrapper covers German notation |
[dbo].[fn_convert_bit](N'YES') | try_cast_boolean('YES') | full parallel |
Summary
Conversion to bit is a two-step thought process. First, check whether TRY_CONVERT(bit, …) covers the source: the built-in understands the integer standard plus 'true'/'false', case-insensitive and locale-independent. Only then, if the source delivers non-standard notations ('J', 'YES', 'ON', 'x', …), reach for a dedicated function [dbo].[fn_convert_bit] with CASE mapping. Both paths return NULL for unknown values — that matches the three-valued logic (0/1/NULL) of the bit type.
Take-Away:
TRY_CONVERT(bit, …)first. The built-in covers more than the first reflex “I need my own function” suggests.fn_convert_bitonly for non-standard notations. Per data source, theCASElist is adapted to the values actually delivered.@p_value AS nvarchar(50)as the default parameter type. Whitespace padding from the source does not break before theTRIM.- Postgres counterpart via
s::boolean(built-in) andtry_cast_boolean(wrapper for non-standard notations). Advantage over SQL Server:booleanis native, and the built-in accepts a broader yes/no list out of the box thanTRY_CONVERT(bit, …).
FAQ
Why does TRY_CONVERT(bit, 'J') return NULL?
The bit parser only knows integer strings ('0', '1', '-1', any number other than 0 → 1) and the literals 'true'/'false' (case-insensitive). 'J' is none of those, so the call falls back to NULL. In an ETL path that is intentional — TRY_CONVERT does not raise; it signals “does not fit” via NULL. For yes/no notations like 'J', a dedicated function with CASE mapping is required (see [dbo].[fn_convert_bit]).
What’s the difference between bit and boolean?
SQL Server has no native BOOLEAN data type for columns or variables. Boolean expressions exist only internally in predicates and conditions (IS NULL, EXISTS, WHERE clauses). If you need to store truth values in a table, bit is the substitute. Postgres, on the other hand, has boolean as a native data type and accepts a broader yes/no list on cast ('t'/'true'/'y'/'yes'/'on'/'1' and their counterparts). Practically: in a cross-engine ETL pipeline, bit (SQL Server) ↔ boolean (Postgres) is the semantic counterpart, and the value range is identical (0/1/NULL respectively true/false/NULL).
How do I store bit efficiently in a wide table?
SQL Server stores multiple bit columns of the same row together in a single byte (up to 8 columns in 1 byte). The storage engine handles the grouping internally — you do not need to reorder columns in the CREATE TABLE definition. For a single bit column there is no measurable storage advantage over tinyint. The difference is primarily semantic: bit signals “three-valued logic with 0/1/NULL”, tinyint signals “small integer 0–255”.
What to do if the source mixes 'YES', 'Y', '1', and 'true'?
Extend the fn_convert_bit function (all four values are already covered) — or preprocess in the SSIS Derived Column transformation if the conversion happens in the pipeline rather than in T-SQL. The key is exhausting the list: every notation actually present in the source must be in the CASE list, otherwise records land on NULL and the import looks incomplete. When in doubt: per data source, do an inventory of distinct values (SELECT DISTINCT col FROM stage or similar) and pin the list down.
Postgres counterpart to TRY_CONVERT(bit, …)?
Directly: s::boolean (see Postgres bridge). The built-in already covers 't'/'true'/'y'/'yes'/'on'/'1' and their counterparts — more than SQL Server’s bit cast. But: it raises an exception instead of returning NULL on invalid input. For equivalent fault tolerance, a try_cast_boolean PL/pgSQL wrapper analogous to the one in TRY_CONVERT // Converting data to bigint, int, smallint, tinyint is needed. Starting with Postgres 18 (late 2025), try_cast is built in.
Related Posts
- Data quality in an ETL process
- TRY_CONVERT // Converting data to decimal, numeric
- TRY_CONVERT // Converting data to bigint, int, smallint, tinyint
- TRY_CONVERT // Converting data to money, smallmoney
- TRY_CONVERT // Converting data to float, real
- TRY_CONVERT // Converting data to date, datetime, datetime2, time