Sql-server – represent the value of gender field with ‘male’ if 1 and ‘female’ is 0

sql serversql-server-2012

Need to convert gender field has data type as bit.

I used this syntax:

Select case when CAST([GenderIsMale] AS bit)=1 then 'Male' 
when CAST([GenderIsMale] AS bit)=0 then 'Female' 
else  CAST([GenderIsMale] AS bit) 
end

Returns the error:

Conversion failed when converting the varchar value 'Male' to data type bit.

Best Answer

The problem is that you have two different return types, depending on the value of [GenderIsMale]; the two WHEN conditions return a string while the ELSE returns a BIT. It is easier to see if you format the code nicely:

Select case
    when CAST([GenderIsMale] AS bit)=1 then 'Male'   -- return type = VARCHAR
    when CAST([GenderIsMale] AS bit)=0 then 'Female' -- return type = VARCHAR
    else CAST([GenderIsMale] AS bit)                 -- return type = BIT
end

You can test this as follows:

DECLARE @GenderIsMale INT = 1;

Select case
    when CAST(@GenderIsMale AS bit)=1 then 'Male'
    when CAST(@GenderIsMale AS bit)=0 then 'Female'
    else CAST(@GenderIsMale AS bit)
end

Returns:

Msg 245, Level 16, State 1, Line 28
Conversion failed when converting the varchar value 'Male' to data type bit.

You can fix this by returning a string (or literal NULL) in the ELSE condition (pick one of those ELSE conditions, depending on what you are trying to accomplish):

SELECT CASE CAST([GenderIsMale] AS BIT)
    WHEN 1 THEN 'Male'
    WHEN 0 THEN 'Female'
    ELSE 'other string'
    --ELSE CONVERT(VARCHAR(50), [GenderIsMale]) -- if column is a numeric type
    --ELSE [GenderIsMale] -- if column is a string type
    --ELSE NULL
END;

Please note:

  • Depending on the datatype of the GenderIsMale column and the data in it, it is possible that there is an additional source of the error: the CAST of the value into BIT. For example, if the datatype is VARCHAR, then values of empty string, 'true', 'false', and strings of just digits 0 - 9 (no spaces, commas, periods, or minus sign) will convert, but anything else will error. Since you are using SQL Server 2012, you can switch to using TRY_CAST (or even TRY_CONVERT) to get around that.

  • The commented-out ELSE conditions are mainly to show the possible variations for getting the resulting expression to be consistent across all branches of the CASE statement. But in the scenario here of having a BIT / boolean value, if the datatype of the GenderIsMale column is a number, then the only value that could fall through to the ELSE is a NULL, hence you would use ELSE NULL. But if the datatype is a string, then you would use ELSE [GenderIsMale].

Related Question