December 5, 2023 at 6:57 pm
How do I add 0 in the beginning but the length of the value cannot exceed 5 digit. For example, for ID 123, it should show 00123 And for ID 23, it should show 00023.
SELECT RIGHT(00000 + convert(varchar(5), CustID), 5) from my table
SELECT RIGHT(00000 + REPLICATE(CustID, 5), 5) from my table
I have tried multiple functions but they are not working for me. The result still shows 23 instead of 00023 or 123 instead of 00123.
Any help is highly appreciated.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 5, 2023 at 7:14 pm
00000 is an integer, so you are explicitly converting your value to char, but then implicitly converting it back to integer when you add it to 00000. You want to use '00000'.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2023 at 7:21 pm
Put the zeros in quotes.
SELECT RIGHT('00000' + convert(varchar(5), CustID), 5) from my tableI thought CONCAT might work without quotes but it appears to convert 00000 to 0 before implicitly casting to varchar, so I got 023, not 00023.
RIGHT(CONCAT(00000, CustID),5),
RIGHT(CONCAT('00000', CustID),5),
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply