Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – How To Convert From Boolean(bit) to String’

Bit (Boolean) data type keeps result in 1 or 0 form in the SQL Server database. But most of the time we need to display 0 as FALSE and 1 as TRUE in front end applications. Given below is the shortest possible solution using IIF function in SQL Server 2012.

You can also convert it using CASE statement but the syntax will be longer.

Given below are the two methods you can use to convert Boolean(bit) data type into string.

Method 1:
In this method, we will use IIF function to convert boolean(bit) to string. IIF function is a new logical function shipped with SQL Server 2012.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
SELECT
  [BusinessEntityID]
, [Name]
, [ActiveFlag]
, IIF([ActiveFlag]=1,'TRUE','FALSE') AS [ActiveFlag As String]
FROM [Purchasing].[Vendor]
GO
--OUTPUT

boolean_to_string1.1

Method 2:
In this method, we will use CASE statement to convert boolean(bit) to string.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
  [BusinessEntityID]
, [Name]
, [ActiveFlag]
, CASE WHEN [ActiveFlag]=1 THEN 'TRUE'
       WHEN [ActiveFlag]=0 THEN 'FALSE'
  END AS  [ActiveFlag As String]
FROM [Purchasing].[Vendor]
GO
--OUTPUT

boolean_to_string1.2

Conclusion :
The only difference between the above two methods is the Syntax. Apart from this, each and every activity, including the performance, will be the same. As per MSDN, IIF is a shorthand form for writing a CASE expression.

Advertisements

Read Full Post »