Sunday, February 12, 2012

Casting bit data type to represent something else

HI all,
I have inhereted a table that has a bit datatype with 0 representing Male
gender, and 1 Representing female gender
Is there a way I can cast/convert this to have it display M ... or F in my
select statement
Thanks
RobertSelect CASE columnName WHEN 0 THEN 'M' ELSE 'F' END AS gender ...
"Robert Bravery" <me@.u.com> wrote in message
news:u5zANm2JGHA.3936@.TK2MSFTNGP12.phx.gbl...
> HI all,
> I have inhereted a table that has a bit datatype with 0 representing Male
> gender, and 1 Representing female gender
> Is there a way I can cast/convert this to have it display M ... or F in my
> select statement
> Thanks
> Robert
>|||A bit column is nullable, so we need to adjust the select case like so:
Select CASE columnName WHEN 0 THEN 'M' WHEN 1 THEN 'F' ELSE columnName END
AS gender ...
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e62Ywo2JGHA.528@.TK2MSFTNGP12.phx.gbl...
> Select CASE columnName WHEN 0 THEN 'M' ELSE 'F' END AS gender ...
> "Robert Bravery" <me@.u.com> wrote in message
> news:u5zANm2JGHA.3936@.TK2MSFTNGP12.phx.gbl...
>|||In his post he never mentionned NULL, so I didn't include it.
Maybe the column is not Nullable.
...so there!!! :-|
"JT" <someone@.microsoft.com> wrote in message
news:eCPq%2312JGHA.500@.TK2MSFTNGP15.phx.gbl...
>A bit column is nullable, so we need to adjust the select case like so:
> Select CASE columnName WHEN 0 THEN 'M' WHEN 1 THEN 'F' ELSE columnName END
> AS gender ...
>
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:e62Ywo2JGHA.528@.TK2MSFTNGP12.phx.gbl...
>|||Robert Bravery wrote:
> HI all,
> I have inhereted a table that has a bit datatype with 0 representing Male
> gender, and 1 Representing female gender
> Is there a way I can cast/convert this to have it display M ... or F in my
> select statement
> Thanks
> Robert
SELECT
CASE gender
WHEN 0 THEN 'M'
WHEN 1 THEN 'F'
FROM your_table ;
Fix the column. The proper ISO codes for gender are 0 = Unknown, 1 =
Male , 2 = Female and 9 = Not Applicable so the values for your bit
column are potentially very confusing. Personally I'd use M and F in a
CHAR(1) column.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
Thanks a million,
Robert
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e62Ywo2JGHA.528@.TK2MSFTNGP12.phx.gbl...
> Select CASE columnName WHEN 0 THEN 'M' ELSE 'F' END AS gender ...
> "Robert Bravery" <me@.u.com> wrote in message
> news:u5zANm2JGHA.3936@.TK2MSFTNGP12.phx.gbl...
Male
my
>|||HI,
Yes you are correct. NULL's not allowed
Thanks
Robert
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eKlw872JGHA.208@.tk2msftngp13.phx.gbl...
> In his post he never mentionned NULL, so I didn't include it.
> Maybe the column is not Nullable.
> ...so there!!! :-|
> "JT" <someone@.microsoft.com> wrote in message
> news:eCPq%2312JGHA.500@.TK2MSFTNGP15.phx.gbl...
END
in
>|||I was just razzing JT for going pedantic on me. :-)
"Robert Bravery" <me@.u.com> wrote in message
news:%23p2jAL3JGHA.344@.TK2MSFTNGP11.phx.gbl...
> HI,
> Yes you are correct. NULL's not allowed
> Thanks
> Robert|||Ok. I'm new here so don't know all the politics
:)
Robert
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:OP9j7P3JGHA.1760@.TK2MSFTNGP10.phx.gbl...
> I was just razzing JT for going pedantic on me. :-)
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23p2jAL3JGHA.344@.TK2MSFTNGP11.phx.gbl...
>|||Hi David,
Thanks for the reply.
Not my table, inherited it, so stuck with these values
Thanks
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138824296.130937.296000@.g44g2000cwa.googlegroups.com...
> Robert Bravery wrote:
Male
my
> SELECT
> CASE gender
> WHEN 0 THEN 'M'
> WHEN 1 THEN 'F'
> FROM your_table ;
> Fix the column. The proper ISO codes for gender are 0 = Unknown, 1 =
> Male , 2 = Female and 9 = Not Applicable so the values for your bit
> column are potentially very confusing. Personally I'd use M and F in a
> CHAR(1) column.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

No comments:

Post a Comment