Error in inserting leading zeroes in SQL Server 2000

Question

I use this code to add leading zeroes.

SELECT RIGHT('HL000000'+ CONVERT(VARCHAR(10), @holrefno + 1), 10) AS 'refno'

But when I tested it, let's say I'll make the reference number as 99 it would show up as HL00000100. I want to keep it as a 10 digit string in my database, could anyone refer me my mistake? Or is there any way I could fix this?

EDIT:

I would like to have an input where the string length will still be 10.

still not the answer, because the string length becomes 11 instead of 10 — , Aug 27 '15 at 03:55
The result of `right(x,10)` cannot be a string of length 11. It can only be a string of length 10 — Nick.McDermaid, Aug 27 '15 at 03:59
Do the 10 digits you want _include_ or _exclude_ the HL part? "HL00000100" is a ten digit code including the HL part. — Nick.McDermaid, Aug 27 '15 at 04:05
Can `@holrefno` ever be less than -1 or greater than 999998? — Andrew, Aug 27 '15 at 04:15
It can not be less than 1 sirm but it could reach 999999, but its another problem sir. as of now, i just want a 10 length string as a reference number . — , Aug 27 '15 at 04:53
sorry sir for the late reply, but i checked your answer sir, and it went well! thanks sir! — , Sep 01 '15 at 07:33

score 0 · Accepted Answer · answered Aug 27 '15 at 04:05

0

You just have to remove the 'HL' part of the right function (therefore also limit your varchar to 8), and also add a '0' in case @holrefno is less than 9.

SELECT 'HL' + right('0000000'+ CONVERT(VARCHAR(8), @holrefno + 1), 8) AS 'refno'

answered Aug 27 '15 at 04:05

Andrew

7,049
2
32
41

score 0 · Answer 2 · answered Aug 27 '15 at 05:30

0

Below one is working even though @holrefno is null

declare @holrefno int
--set @holrefno = 56784

SELECT 'HL' + right('0000000'+ CONVERT(VARCHAR(8), @holrefno + 1), 8) AS 'refno'

answered Aug 27 '15 at 05:30

Indra Prakash Tiwari

877
1
6
15

That looks pretty much like the accepted answer. ;) I bet you meant `ISNULL(@holrefno, 0)`. – Andrew Aug 27 '15 at 18:30

Error in inserting leading zeroes in SQL Server 2000

2 Answers2