TRY_CONVERT // Converting data to bit

marcus

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 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 Y and N for Yes and No or J and N for Ja and No (German). But you can also find ON and OFF, an X or a minus sign for No and much more.

SQL Server 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 NULL in the event of an error – analogous to the TRY_CONVERT function. This article introduces such a function [dbo].[fnConvertBit]. Expected values are hard-coded in this function and translated into a 0 or 1. A NULL 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 p_Value is of type nvarchar. If values of type No Text are passed, SQL Server implicitly converts the transferred value into a value of type nvarchar.

[dbo].[fnConvertBit]

Converts a transferred input value into the target data type bit. If the input value cannot be converted, NULL is returned. The transferred value is treated as case insensitive.

Syntax

[dbo].[fnConvertBit](@p_value AS nvarchar(5))

Arguments

  • p_value
    Indicates the input value to be converted.

Return

Returns the converted value as a value of type bit if the conversion is successful. If the input value cannot be converted, NULL is returned.

Supported input values

TrueFalse
‘J’‘N’
‘JA’‘NEIN’
‘Y’‘N’
‘YES’‘NO’
‘1’, ‘-1’‘0’
‘ON’‘OFF’
‘TRUE’‘FALSE’
‘x’‘-‘

Code

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

Und hier einige Beispiele…

 1: SELECT [dbo].[fnConvertBit](N'1');     -- 1
 2: SELECT [dbo].[fnConvertBit](N'0');     -- 0
 3: SELECT [dbo].[fnConvertBit](N'J');     -- 1
 4: SELECT [dbo].[fnConvertBit](N'ja');    -- 1
 5: SELECT [dbo].[fnConvertBit](N'N');     -- 0
 6: SELECT [dbo].[fnConvertBit](N'nein');  -- 0
 7: SELECT [dbo].[fnConvertBit](N' ');     -- NULL
 8: SELECT [dbo].[fnConvertBit](N'X');     -- NULL
 9: SELECT [dbo].[fnConvertBit](NULL);     -- NULL
10: SELECT [dbo].[fnConvertBit](N'true');  -- 1
11: SELECT [dbo].[fnConvertBit](N'false'); -- 0
12: SELECT [dbo].[fnConvertBit](N'ON');    -- 1
13: SELECT [dbo].[fnConvertBit](N'OFF');   -- 0
14: SELECT [dbo].[fnConvertBit](N'-');     -- 0
15: SELECT [dbo].[fnConvertBit](NULL);     -- 1

Related Posts