# 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
``````

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
``````

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
###### 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.

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
``````

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

I have 127 records, and this returns 0127.
0
Change the two 4s in the posted code to 3 or to whatever you want the minimum length to be.
0
Product 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
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
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
Product 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
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
``````

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

1 digit should be 01
2 digits should be 02
3 digits should be 300
4 digits should be 4000
0
Senior 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

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

Product 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.