Converting string to bit (with null possibility)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting string to bit (with null possibility)

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2007-05-30 : 02:34:43

I have a subquery in a larger stored procedure that returns a set of string values. These values are always '1', '0' or null. I am using this subquery as part of another query and would like to convert these string values to bit values (booleans, essentially)

So, my code looks like

SELECT a.colA, a.colb, CONVERT(bit, NULLIF(b.bitstring,'')) AS mybitvalue

FROM tableA a

LEFT JOIN tableb b ON (b.primary_key=a.primary_key)

I wish for the mybitvalue field to be 1 (or true) when the source string is '1', and 0 (or false) when the source string is '0' or NULL. This works fine if the source string is '0', however, if it is null, my current code simply creates a null bit value... not 0 (or false).

Any ideas? Thanks!

spejbl
Starting Member

28 Posts

Posted - 2007-05-30 : 02:40:53

[code]SELECT
a.colA, a.colb,
CAST(CASE WHEN b.bitstring='1' THEN 1 ELSE 0 END AS BIT) AS mybitvalue
FROM tableA AS a
LEFT JOIN tableB AS b ON b.primary_key=a.primary_key[/code]

--
Tom
Microsoft KB articles monitoring | Apple KB articles monitoring

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 02:46:18

Try this...

cast(coalesce(b.bitstring, 0) as bit) AS mybitvalue

S.Ahamed

akashenk
Posting Yak Master

111 Posts

Posted - 2007-05-30 : 02:48:02

I tried spejbl's suggestion and it works. Is there any advantage to your method, pbguy? Wouldn't coalesce ignore the null values?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 02:51:01

Coalesce function will give the value we specified if the data is null

--------------------------------------------------
S.Ahamed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 02:55:17

quote:
Originally posted by akashenk

I tried spejbl's suggestion and it works. Is there any advantage to your method, pbguy? Wouldn't coalesce ignore the null values?

coalesce will convert null to 0 in that example

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 02:58:31

quote:
Originally posted by pbguy

Try this...

cast(coalesce(b.bitstring, 0) as bit) AS mybitvalue

S.Ahamed

Note that all non-zero values will be converted to 1

Madhivanan

Failing to plan is Planning to fail

akashenk
Posting Yak Master

111 Posts

Posted - 2007-05-30 : 02:59:28

Ahh. I see that now. Does anyone have any idea if coalesce will perform better or worse than the direct cast and case statement offered above in spejbl's post?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 03:06:44

Its just short form of CASE WHEN
For speed, refer this
http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

Madhivanan

Failing to plan is Planning to fail

akashenk
Posting Yak Master

111 Posts

Posted - 2007-05-30 : 03:57:10

Its a bit hard to tell form that link, but am I to assume that

cast(isnull(b.bitstring, 0) as bit) AS mybitvalue

would be faster than

cast(coalesce(b.bitstring, 0) as bit) AS mybitvalue

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 05:17:13

Yes it is

Madhivanan

Failing to plan is Planning to fail

akashenk
Posting Yak Master

111 Posts

Posted - 2007-05-30 : 11:55:31

Thanks for everone's help!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-30 : 14:23:01

ISNULL will work in this case and be faster than COALESCE. But, I always use COALESCE for ANSI compliance as well as to avoid a potential issue that ISNULL can cause. Consider this:[CODE]DECLARE @Foo VARCHAR(5)

SELECT ISNULL(@Foo, 'Unknown')
SELECT COALESCE(@Foo, 'Unknown')[/CODE]

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources