SELECT COUNT(DISTINCT) returns null when nothing is found instead of 0

tim8w · February 25, 2026, 4:44pm

I have the following code:

SELECT COUNT(DISTINCT Component) AS Found FROM tblComponents WHERE(Component NOT LIKE '%[a-z]%') AND(LTRIM(RTRIM(Component)) = 'GM13622') GROUP BY Component

How do I get the SELECT to return zero instead of NULL when there is no match?

tim8w · February 25, 2026, 5:57pm

Got the answer on another Forum…

SELECT COUNT(DISTINCT Component) AS 'Found' FROM tblComponents WHERE(Component NOT LIKE '%[a-z]%') AND(LTRIM(RTRIM(Component)) = 'GM13622')

JeffModen · February 26, 2026, 12:43am

Unless your database is set to be case sensitive, your code will never find anything because of you doing the NOT '%[a-z]%'.

You also have some serious data issues of you have to do the LTRIM(RTRIM)) thing to Component, which will also make your query Non0SARGable (always results in a scan)… which your NOT LIKE might also do.

harishgg1 · April 1, 2026, 8:11am

hi

hope this helps

DROP TABLE tblComponents

CREATE TABLE  tblComponents (Component VARCHAR(100))

INSERT INTO tblComponents SELECT 'GM13622'
INSERT INTO tblComponents SELECT 'RT12223'
INSERT INTO tblComponents SELECT 'aa13456'

SELECT 
     nullif(COUNT(DISTINCT Component),0) AS Found 
FROM 
   tblComponents 
WHERE
   Component <> UPPER(Component) COLLATE Latin1_General_CS_AS
      AND(LTRIM(RTRIM(Component)) = 'GM13622')

harishgg1 · April 1, 2026, 9:03am

Lots and lots of other parameters and stuff goes into this

not discussing all those