I'm trying to return to my web application, a number for the volumne of messages, which are in the system for today. I have the following piece of SQL which is working fine:
SELECT RIGHT(REPLICATE('0', 2) + CAST(COUNT(*) AS VARCHAR(3)), 3) AS CountForToday
Which, for example would return a count of :
I need to cater, for the number being bigger than 3 digits, but If I change the above to this :
SELECT RIGHT(REPLICATE('0', 2) + CAST(COUNT(*) AS VARCHAR(4)), 4) AS CountForToday
Obviously, this also required a manual change of the TSQL script. So, my question is, how can I dynamically, adjust the VARCHAR size, to cater for the number being returned, without leading zeros?