Dynamic VARCHAR lenght on a count

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

Open in new window


Which, for example would return a count of :
127

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

Open in new window


I get
0127

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?
LVL 1
Wayne AthertonProduct Analyst Financial Messaging LondonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Try this:

SELECT RIGHT(REPLICATE('0', 2) + CAST(COUNT(*) AS VARCHAR), CASE WHEN LEN(CAST(COUNT(*) AS VARCHAR)) > 4 THEN LEN(CAST(COUNT(*) AS VARCHAR)) ELSE 4 END) AS CountForToday

Open in new window


Change 4 to the minimum length you want.
0
Wayne AthertonProduct Analyst Financial Messaging LondonAuthor Commented:
Hi Shaun,

I have 127 records, and this returns 0127.
0
Shaun KlineLead Software EngineerCommented:
Change the two 4s in the posted code to 3 or to whatever you want the minimum length to be.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Wayne AthertonProduct Analyst Financial Messaging LondonAuthor Commented:
Yeah, I can do this, but it negates my question. I need to do this automatically, based on the number. So, if it returns 1000 records, I need the varchar to return 4 digits, ifthere 100, then 3... so on and so forth.
0
Shaun KlineLead Software EngineerCommented:
Exactly. In the CASE statement, it checks for the length of count. If it is greater than your desired minimum length (3), the CASE statement returns the length without any padded zeros. If it is less than or equal to your minimum desired length, it left pads zeros and returns the minimum desired length.

The code I posted just used 4, but you can make it whatever you desire. You could even wrap that into a scalar function, where you pass in the value and the desired minimum length, and have the function return a left, zero-padded string containing your value.
0
Shaun KlineLead Software EngineerCommented:
Re-reading your question, if your count returns a single digit (say 1) count, you want it to be length 3 (001), correct? If your count returns a two-digit (say 23) count, you want it to be length 3 (023)? You state that a count that is three digits or greater should just return the count with no zero-padding. Is this correct?
0
Wayne AthertonProduct Analyst Financial Messaging LondonAuthor Commented:
No no. So, apologies, if i've confused. If the count is 1 then I need it to display 01. Of it's 3 digits, then 100, if its 4 then 10000. My problem is being able to dynamically determin the length to display
0
Shaun KlineLead Software EngineerCommented:
Let's simplify. Does this code produce the desired output for 1, 2, 3 and 4 digits?

SELECT RIGHT(REPLICATE('0', 2) + CAST(1 AS VARCHAR), CASE WHEN LEN(CAST(1 AS VARCHAR)) > 3 THEN LEN(CAST(1 AS VARCHAR)) ELSE 3 END) AS Output_1Digit,
	RIGHT(REPLICATE('0', 2) + CAST(10 AS VARCHAR), CASE WHEN LEN(CAST(10 AS VARCHAR)) > 3 THEN LEN(CAST(10 AS VARCHAR)) ELSE 3 END) AS Output_2Digits,
	RIGHT(REPLICATE('0', 2) + CAST(100 AS VARCHAR), CASE WHEN LEN(CAST(100 AS VARCHAR)) > 3 THEN LEN(CAST(100 AS VARCHAR)) ELSE 3 END) AS Output_3Digits,
	RIGHT(REPLICATE('0', 2) + CAST(1000 AS VARCHAR), CASE WHEN LEN(CAST(1000 AS VARCHAR)) > 3 THEN LEN(CAST(1000 AS VARCHAR)) ELSE 3 END) AS Output_4Digits

Open in new window


If not, what would you expect for these values: 1, 10, 100, 1000?
0
Wayne AthertonProduct Analyst Financial Messaging LondonAuthor Commented:
Output below

Output of sql
1 digit should be 01
2 digits should be 02
3 digits should be 300
4 digits should be 4000
0
Scott PletcherSenior DBACommented:
SELECT CASE WHEN COUNT(*) < 10 THEN '0' ELSE '' END + CAST(COUNT(*) AS VARCHAR(5)) AS CountForToday
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne AthertonProduct Analyst Financial Messaging LondonAuthor Commented:
Hi ScottPletcher,

this works, just testing all combinations.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.