How to display sql server boolean type True & False for 1 & NULL ?

1.4K Asked by CarolineBrown in SQL Server , Asked on Apr 22, 2021

In my query I am able to get column values as 1 and NULL. I would like to display True & False for 1 & NULL? /*Declare Variable*/ DECLARE @Pivot_Column [nvarchar](max); DECLARE @Query [nvarchar](max); /*Select Pivot Column*/ SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(PracticeLocationServiceCode) FROM (SELECT DISTINCT [PracticeLocationServiceCode] FROM PracticeLocationService)Tab /*Create Dynamic Query*/ SELECT @Query=' ;WITH ServiceOffered AS ( SELECT PracticeLocationID, '+@Pivot_Column+'FROM (SELECT PracticeLocationID, PracticeLocationServiceCode,CAST(ServiceFlag AS INT) ServiceFlag FROM PracticeLocationService )Tab1 PIVOT ( MAX(ServiceFlag) FOR [PracticeLocationServiceCode] IN ('+@Pivot_Column+')) AS Tab2 ) SELECT REGION,LocationShortName,GLCODE,C.* FROM ServiceOffered C INNER JOIN VWLOCATIONS V ON V.PracticeLocationID = C.PracticeLocationID ORDER BY REGION,LocationShortName ' /*Execute Query*/ EXEC sp_executesql @Query Does SQL Server have Boolean data type?

Answered by bhushan bhad

SQL server boolean data type=can be TRUE , FALSE or UNKNOWN . However, the boolean data type is only the result of a boolean expression containing some combination of comparison operators (e.g. = , <> , < , >= ) or logical operators (e.g. AND , OR , IN , EXISTS ).

  Alternatively, you can use strings 'true' and 'false', but these are strings just like any other string. Often the bit type is used instead of Boolean as it can only have values 1 and 0. Typically 1 is used for "true" and 0 for "false". It is still an integer type, though. Boolean logic will not work. For example, if (CONVERT(bit, 1) and CONVERT(bit, 1))... is not legal syntax.

Likely the best option is to use a CASE and return the requisite strings, as @Akina commented: CASE WHEN TheColumn = 1 THEN 'True' WHEN TheColumn IS NULL THEN 'False' ELSE 'Oops!' END

Your Answer

Answer (1)

Ranjana

In SQL Server, the BIT data type is used to represent boolean values. A BIT can have a value of 0, 1, or NULL. To display TRUE and FALSE for 1 and NULL respectively, you can use the CASE statement to convert these values in your query.

Here’s an example query demonstrating how to do this:

Example Table:

Assume you have a table named ExampleTable with a column named IsActive of type BIT.

  CREATE TABLE ExampleTable (    ID INT PRIMARY KEY,    IsActive BIT);

  INSERT INTO ExampleTable (ID, IsActive) VALUES (1, 1), (2, NULL), (3, 0);

Query to Display TRUE and FALSE:

You can use the CASE statement to convert the BIT values to TRUE or FALSE.

  SELECT     ID,    CASE         WHEN IsActive = 1 THEN 'TRUE'        WHEN IsActive = 0 THEN 'FALSE'        ELSE 'FALSE'  -- Defaulting NULL to 'FALSE'    END AS IsActiveStatusFROM     ExampleTable;In this query:

If IsActive is 1, it displays TRUE.

If IsActive is 0 or NULL, it displays FALSE.

If you specifically want to distinguish between NULL and 0, you can modify the CASE statement accordingly:

  SELECT     ID,    CASE         WHEN IsActive = 1 THEN 'TRUE'        WHEN IsActive = 0 THEN 'FALSE'        WHEN IsActive IS NULL THEN 'FALSE'  -- Explicitly handling NULL    END AS IsActiveStatusFROM     ExampleTable;

In this version, you explicitly check for NULL values to ensure they are displayed as FALSE.

Displaying Custom Text:

If you need more customized text for TRUE and FALSE, simply replace 'TRUE' and 'FALSE' with your desired strings.

  SELECT     ID,    CASE         WHEN IsActive = 1 THEN 'Active'        WHEN IsActive = 0 THEN 'Inactive'        ELSE 'Inactive'  -- Defaulting NULL to 'Inactive'    END AS IsActiveStatusFROM     ExampleTable;

This approach ensures that your query result displays human-readable boolean values based on the BIT column in your SQL Server table.

4 Months