CASE | SQL Server Portal

Posts Tagged ‘CASE’

SQL SERVER – String comparison as a Boolean in Select statement

Posted in Solutions, SQL SERVER, tagged CASE, IIF, raresql, SQL, SQL Server on July 24, 2013| Leave a Comment »

Most of the times, we usually maintain the status column of any table in varchar data type. But sometimes due to the requirements, we need to convert this Varchar data type column to Boolean data type to further use it for stats or other purposes as well. But how ?
Before proceeding with the solution, let me first create a sample to demonstrate the solution.

USE tempdb
GO
CREATE TABLE tbl_sample
(
[ID] int,
[Name] varchar(50),
[Status] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'SQL SERVER 2000','Inactive')
INSERT INTO tbl_sample VALUES (2,'SQL SERVER 2005','Active')
INSERT INTO tbl_sample VALUES (3,'SQL SERVER 2008','Active')
INSERT INTO tbl_sample VALUES (4,'SQL SERVER 2012','Active')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

Given below are the solutions.

Solution 1:
In this solution, we will use CASE statement to convert varchar status to boolean.

--This solution is compatibile with SQL Server 2005 and above.
SELECT
[ID]
,[NAME]
,[STATUS]
,(CASE WHEN [STATUS]='Active' THEN 1 ELSE 0 END) [Boolean Status]
FROM tbl_sample
GO
--OUTPUT

Solution 2:
In this solution, we will use IIF statement to convert varchar status to Boolean.

--This solution is compatibile with SQL Server 2012 and above.
SELECT
[ID]
,[NAME]
,[STATUS]
,IIF([STATUS]='Active', 1,0) [Boolean Status]
FROM tbl_sample
GO
--OUTPUT