How do I do this...

Question

Post reply

How do I do this...

Rafferty Uy

SSC-Addicted

Points: 428
More actions
February 21, 2005 at 1:10 am

#65990

Hi, I just can't figure out an SQL statement for this.
For example I have a table MyTable with attributes ( ID, Record1, Record2, Record3 ). ID being the primary key, and the Records 1-3 being strings which are Nullable or could be empty strings.
What can I do to select / count the number of Records (among the 3) have values (not null and not empty string)?
Thanks,
~Rafferty

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 1

SELECT COUNT(*) FROM MyTable

WHERE ( Record1 is Not Null and Record2 is Not Null and Record3 is Not Null)

AND ( LTRIM(RTRIM(Record1)) = '' and LTRIM(RTRIM(Record2)) = '' and LTRIM(RTRIM(Record1)) = '' )

Thanks,

Ganesh

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 2

SELECT COUNT(*) FROM MyTable

WHERE ( Record1 is Not Null and Record2 is Not Null and Record3 is Not Null)

AND ( LEN(LTRIM(RTRIM(Record1))) > 0 and LEN(LTRIM(RTRIM(Record2))) > 0 and LEN(LTRIM(RTRIM(Record3))) > 0 )

Sorry, please find this query

Rafferty Uy SSC-Addicted Points: 428 More actions · Answer 3

hm.. i meant, for example MyTable contains:

ID | Record1 | Record2 | Record3

1 | 'data' | NULL | NULL

2 | NULL | NULL | NULL

3 | 'abc' | NULL | 'def'

after the sql query, i'll get something like

count

1

0

2

Johan Bijnens SSC Guru Points: 135265 More actions · Answer 4

select id

, case when isnull(ltrim(record1),'') = '' then 0 else 1 end

+ case when isnull(ltrim(record2),'') = '' then 0 else 1 end

+ case when isnull(ltrim(record3),'') = '' then 0 else 1 end

as Record_counter

from MyTable

order by id

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Rafferty Uy SSC-Addicted Points: 428 More actions · Answer 5

Wow! didn't think that was possible

Thanks much!

~Rafferty

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 6

I think, no need to use isnull function

select id

, case when record1 is null then 0 else 1 end

+ case when record2 is null then 0 else 1 end

+ case when record3 is null then 0 else 1 end

as Record_counter

from MyTable

order by id

Madhivanan

Failing to plan is Planning to fail

Rafferty Uy SSC-Addicted Points: 428 More actions · Answer 7

I think the isnull function is necessary because the record attributes could be an empty string

btw, thanks everyone for the help

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 8

EDIT: Upon a second read, this is identical to the ISNULL version pposted earlier - sorry for the redundancy.

One last version:

select case when NULLIF(RTRIM(Record1),'') IS NULL THEN 0 ELSE 1 END
      +case when NULLIF(RTRIM(Record2),'') IS NULL THEN 0 ELSE 1 END
      +case when NULLIF(RTRIM(Record3),'') IS NULL THEN 0 ELSE 1 END
       AS MyCount

Not different from what's been suggested, but a bit more compact, allowing the NULL, empty string, and apces-only string checks in one step. Get rid of the RTRIM if strings consisting of spaces only should count towards your total.

R David Francis

Rafferty Uy SSC-Addicted Points: 428 More actions · Answer 9

i see... so this is more efficient right? okay thanks!

and isn't LTRIM(...) needed anymore?

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 10

My NULLIF - RTRIM version and alzdba's ISNULL - LTRIM version should be equivalent in efficiency - just an example of two different people looking at things in a slightly different way. The fundamental methodology, and even the functions involved, are basically the same. It's a "glass is half-full/half-empty" sort of situation.

I use RTRIM (and alzdba uses LTRIM) to deal with fields that contain only spaces. In a char field/variable, a value that only contains spaces is the equivalent of the empty string. In most cases, I tend to programatically treat varchar fields the same (even though

'', '  ', and '    '

are three distinct varchar values). Either one returns an empty string when encountering a string that only contains spaces.

R David Francis