Better way to check row for values?

I have a group of fields that I want to check for len() = 0. If one of
them in that group is blank len()=0 then I will set some output
parameter to yes. Then the client will report to the user that they need
to complete that section.
My solution now is to set a each field to a variable and if any one of
them are 0 (using or), set the parameter.
Is there a better way to do this?
I was thinking it would be great if there was a function that would just
return true or false if any field in that function was null/len()=0
Example
IFEmpty(Field1, Field2, etc) return True
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot comyes, use NullIf in combination with Coalesce...
NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
Null, or if ColA = '' (empty)
Then compare Coalesce of all columns together
If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
NullIf(ColD, ''), etc, ) Is Null,
-- Then at one (or more) of them is null or empty
"Steve Lewis - Website Nation" wrote:

> I have a group of fields that I want to check for len() = 0. If one of
> them in that group is blank len()=0 then I will set some output
> parameter to yes. Then the client will report to the user that they need
> to complete that section.
> My solution now is to set a each field to a variable and if any one of
> them are 0 (using or), set the parameter.
> Is there a better way to do this?
> I was thinking it would be great if there was a function that would just
> return true or false if any field in that function was null/len()=0
> Example
> IFEmpty(Field1, Field2, etc) return True
>
> --
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>|||Cool, thanks, I will check that out. I have used NullIf before, but not
Coalesce. That looks like it will solve my problem.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/9/2005 8:49 PM, CBretana wrote:
> yes, use NullIf in combination with Coalesce...
> NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
> returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
> Null, or if ColA = '' (empty)
> Then compare Coalesce of all columns together
> If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
> NullIf(ColD, ''), etc, ) Is Null,
> -- Then at one (or more) of them is null or empty
> "Steve Lewis - Website Nation" wrote:
>|||I believe that your suggestion will test whether all of the fields are empty
.
Whereas the original poster was trying to test for any one of the fields
being empty.
You could try something like...
create table testtable
(
colA varchar(10),
colB varchar(10)
)
go
insert into testtable values ('', '')
insert into testtable values (null, null)
insert into testtable values ('a', '')
insert into testtable values ('a', null)
insert into testtable values (null, 'a')
insert into testtable values ('', 'a')
insert into testtable values ('a', 'a')
go
select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb,
''), 1, 1) is null then 1 else 0 end from testtable
go
drop table testtable
"CBretana" wrote:
> yes, use NullIf in combination with Coalesce...
> NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
> returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
> Null, or if ColA = '' (empty)
> Then compare Coalesce of all columns together
> If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
> NullIf(ColD, ''), etc, ) Is Null,
> -- Then at one (or more) of them is null or empty
> "Steve Lewis - Website Nation" wrote:
>|||Yes, I am trying to test for any one of the fields. But, between the two
if you, I think I have an answer.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/9/2005 9:55 PM, Mike Gemmell wrote:
> I believe that your suggestion will test whether all of the fields are emp
ty.
> Whereas the original poster was trying to test for any one of the fields
> being empty.
> You could try something like...
> create table testtable
> (
> colA varchar(10),
> colB varchar(10)
> )
> go
> insert into testtable values ('', '')
> insert into testtable values (null, null)
> insert into testtable values ('a', '')
> insert into testtable values ('a', null)
> insert into testtable values (null, 'a')
> insert into testtable values ('', 'a')
> insert into testtable values ('a', 'a')
> go
> select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb
,
> ''), 1, 1) is null then 1 else 0 end from testtable
> go
> drop table testtable
> "CBretana" wrote:
>|||Mike,
Right, Coalesce returns the first non-null parameter in the parameter list,
So only if all of them are null, will the whole Coalesce expression will be
null.
Concatenating the NullIfs, as you suggest, is the right answer...
"Mike Gemmell" wrote:
> I believe that your suggestion will test whether all of the fields are emp
ty.
> Whereas the original poster was trying to test for any one of the fields
> being empty.
> You could try something like...
> create table testtable
> (
> colA varchar(10),
> colB varchar(10)
> )
> go
> insert into testtable values ('', '')
> insert into testtable values (null, null)
> insert into testtable values ('a', '')
> insert into testtable values ('a', null)
> insert into testtable values (null, 'a')
> insert into testtable values ('', 'a')
> insert into testtable values ('a', 'a')
> go
> select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb
,
> ''), 1, 1) is null then 1 else 0 end from testtable
> go
> drop table testtable
> "CBretana" wrote:
>|||OK , I see how the concatenation will work, but doesn't
CONCAT_NULL_YIELDS_NULL have to be set ON or is that ON by default?
According to books online:
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with
a string yields a NULL result. For example, SELECT 'abc' + NULL yields
NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
value with a string yields the string itself (the null value is treated
as an empty string). For example, SELECT 'abc' + NULL yields abc.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:03 AM, CBretana wrote:
> Mike,
> Right, Coalesce returns the first non-null parameter in the parameter lis
t,
> So only if all of them are null, will the whole Coalesce expression will
be
> null.
> Concatenating the NullIfs, as you suggest, is the right answer...
>
> "Mike Gemmell" wrote:
>|||OK , i just found that is on by default:
Option Default Setting
Set nocount OFF
Set noexec OFF
Set parseonly OFF
Set concat_null_yields_null ON
Set rowcount 0
Set ansi_defaults ON
Set arithabort ON
Set showplant_text OFF
Set statistics time OFF
Set statistics 10 OFF
Set ansi_nulls ON
Set ansi_null_dflt_on ON
Set ansi_padding ON
Set ansi_warnings ON
Set cursor_close_on_commit OFF
Set implicit_transactions OFF
Set quoted_identifier ON
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:15 AM, Steve Lewis - Website Nation wrote:
> OK , I see how the concatenation will work, but doesn't
> CONCAT_NULL_YIELDS_NULL have to be set ON or is that ON by default?
> According to books online:
> When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with
> a string yields a NULL result. For example, SELECT 'abc' + NULL yields
> NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
> value with a string yields the string itself (the null value is treated
> as an empty string). For example, SELECT 'abc' + NULL yields abc.
>
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 3/10/2005 12:03 AM, CBretana wrote:
>|||Ok , this is what I have. I will test it tomorrow:
select case when substring(nullif(colA, ''), 1, 1) +
substring(nullif(colb, ''), 1, 1) is null then set @.Incomplete = 'Yes'
else set @.Incomplete = 'No' end from tblEmployee where EmployeeID =
@.EmployeeID
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:17 AM, Steve Lewis - Website Nation wrote:
> OK , i just found that is on by default:
> Option Default Setting
> Set nocount OFF
> Set noexec OFF
> Set parseonly OFF
> Set concat_null_yields_null ON
> Set rowcount 0
> Set ansi_defaults ON
> Set arithabort ON
> Set showplant_text OFF
> Set statistics time OFF
> Set statistics 10 OFF
> Set ansi_nulls ON
> Set ansi_null_dflt_on ON
> Set ansi_padding ON
> Set ansi_warnings ON
> Set cursor_close_on_commit OFF
> Set implicit_transactions OFF
> Set quoted_identifier ON
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 3/10/2005 12:15 AM, Steve Lewis - Website Nation wrote:
>|||Why don't you use CHECK constraints to validate the data on entry?
CREATE TABLE foo (... col VARCHAR(10) NOT NULL CHECK (col > '')...)
David Portas
SQL Server MVP
--