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

Transact-SQL
1

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?

2

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')

3

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.