I Want convert bit type to Yes or No
For Example:
SELECT FirstName, LastName, IsMale from members
Results:
Ramy Said 1
Expected Result:
Ramy Said Yes
Add a comment
|
5 Answers
You can do this using a searched case expression:
SELECT
FirstName,
LastName,
CASE WHEN IsMale = 1 THEN 'Yes' ELSE 'No' END AS IsMale
FROM
Members
1 Comment
Kiquenet
If IsMale is NULL ?
Here you go:
SELECT FirstName, LastName, CASE WHEN IsMale = 1 THEN 'Yes' ELSE 'No' END AS Male
FROM members
Basically, you use a CASE statement to allow you to convert the value. If you had three choices, you could still use the case statement and just add another option (obviously you can't have three options with a bit but if the field was an int, etc.) The ELSE statement is the default statement that runs if you don't get a match. In our case, we just use it for No since we can only have yes or no but in the case of a larger CASE statement, you would want to use this as your fall-back field. For example, you could say "Item Not Found" if you were converting items.
Comments
Use a CASE;
SELECT
FirstName,
LastName,
CASE IsMale WHEN 1 THEN 'Yes' ELSE 'No' END AS IsMale
FROM tbl
Comments
Use an IIF() which is available SQL Server 2012 onwards.
There is one another way of achieving this. (shorthand way(maybe not)). That's using IIF(). See below.
SELECT
[FirstName],
[LastName],
IIF([IsMale]=1,'Yes','No') AS [IsMale As String]
FROM
[Members]
answered Jan 26, 2016 at 8:25
Rajeshkumar Kandhasamy
6,4997 gold badges61 silver badges84 bronze badges
2 Comments
Jazza
The question is tagged SQL Server 2005, but the IIF function is only available from SQL Server 2012 onwards.
Rajeshkumar Kandhasamy
@jazza Thanks for the tip. I will update that in my answer. It will be helpful to someone in future.
SQL Server 2012 introduces two new keywords FORMAT and IIF that can provide a more compact means of converting an integer or bit to string:
DECLARE @MyInt int = 123
PRINT 'Hello '+FORMAT(@MyInt,'')
-- (note: the "+0" converts a bit type to int then outputs '0' or '1')
DECLARE @MyBit bit = 0
PRINT 'Hello '+FORMAT(@MyBit+0,'')
But to convert a bit type to Yes or No I would now use:
PRINT 'Hello '+IIF(@MyBit=1,'Yes','No')
Comments
