SQL Server MVP and my friend Madhivanan has asked very interesting question on his blog regarding How to Generate Zero without using Any Numbers in T-SQL. He has demonstrated various methods how one can generate Zero. When I posted note regarding how one he has generated Zero without using number in my blog post for Free Online Training, blog readers have come up with few very interesting answers. I really found them very interesting and here I am listing them with due credit.
Special mention to Andery.ca as the answer Andery provided is the one, I myself come up with after very first look and that is why I had left the same as hint in the original article.
try this
select count(cast(null as int))
or
any false condition
select count(*) where ‘a’=’b’
It seems every currency symbol that SQL Server supports. Return the same value as zero
i tried some as
select € select ¥ select £
select count(*)-count(*)
Vinay Kumar
Another way for generate zero.
select Ascii(‘Y’)-Ascii(‘Y’) OR select LEN(”)
I like Madhivanan’s answer. and it was awesome.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
15 Comments. Leave new
I found out yesterday that if you type “select count(*) ” it returns 1 =D
select 1-1
Interesting question! :)
Another way.. :)
select CHECKSUM(‘x’) – CHECKSUM(‘x’)
Though there are many ways to generate 0, I really like madhivanan’s approach as it tells us that, SQL Server will identify datatype as money, even if it has only currency symbol without any digits
There is other way as well
SELECT CAST(” AS INT)
and
SELECT CAST(” AS BIT)
Interesting question….
another way…
select datediff(dd,getdate(),getdate())
select count(”) – count(”)
Another way
Select $
Return 0.00
need clarification if it’s possible….
Can we get the output other than 1 for the below query without using any table….?
select count(”) / select count(*)
select count(*) where 1=0
SELECT DATEDIFF(dd,GETDATE(),GETDATE())
select len(”)
SELECT PATINDEX(‘A’,’DEF’)
select count(*) where ””