Char to Bit

[imani_technology_spam]

I am importing a table where I need to convert a char(1) with the
values of 't' or 'f' into a bit field with valies of 1 or 0. Is there
a built-in function that does that? I've been searching, but I can't
find an answer.

[Hugo Kornelis]

On 15 Apr 2005 14:25:24 -0700, im*******************@yahoo.com wrote:

I am importing a table where I need to convert a char(1) with the
values of 't' or 'f' into a bit field with valies of 1 or 0. Is there
a built-in function that does that? I've been searching, but I can't
find an answer.

Hi imani,

The best answer is to store it as a CHAR(1) column with values 't' and
'f' and to forget aboout converting to BIT - what do you expect to gain
from it?

The second best answer is to use a CASE expression.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

Do not use bits in SQL. You cannot use them as Booleans in other
Microsoft host languages. They are proprietary. And a good SQL
porgrammer does not write with flags anyway. Do some searching about
bits for the details.

[imani_technology_spam]

I don't have a choice in the matter. I have been told to convert a
char(1) to a bit. So what is the best way to do it within a UDF?

--CELKO-- wrote:

Do not use bits in SQL. You cannot use them as Booleans in other
Microsoft host languages. They are proprietary. And a good SQL
porgrammer does not write with flags anyway. Do some searching about
bits for the details.

[--CELKO--]

The #2 answer is a CASE expression with CAST() functions to be safe.
You will need to document that for the next guy because this is suicide
and you do not want to be blamed for it.

Did you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently within Microsoft's own proprietary languages? You have to
know what the host language will be to do the mapping from Boolean to
bits. Once that decision is made you cannot use another incompatible
host language. And watch out for CLR later.

[imani_technology_spam]

Thanks for the info. I am aware that MS isn't consistent with their
own Booleans. However, I'm the new guy on a very large team, so I have
to deter to them on that issue. Also, I didn't know you could CAST
from char(1) to a bit. I thought the two data types were incompatible.

[Hugo Kornelis]

On 17 Apr 2005 09:33:44 -0700, im*******************@yahoo.com wrote:

Thanks for the info. I am aware that MS isn't consistent with their
own Booleans. However, I'm the new guy on a very large team, so I have
to deter to them on that issue. Also, I didn't know you could CAST
from char(1) to a bit. I thought the two data types were incompatible.

Hi imani,

You're right, you can't just CAST a char(1) to bit, unless the char(1)
holds only '0' and '1' - and even for that case, I'd run a test before
betting any money on it :-)

That's why both Joe (Celko) and I (in my previous reply in this thread)
suggest using a CASE. Joe's suggestion to *ALSO* use a CAST is actually
quite good - not really needed in SQL Server, but it better documents
what you're doing:

CASE WHEN CharColumn = 't' THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)