0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • er... add another two 0's to your string: `'HL00000000'` – Nick.Mc Aug 27 '15 at 03:36
  • 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.Mc 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.Mc 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
  • And doesn't my answer below do what you need? – Andrew Aug 27 '15 at 05:29
  • sorry sir for the late reply, but i checked your answer sir, and it went well! thanks sir! –  Sep 01 '15 at 07:33

2 Answers2

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'
Andrew
  • 7,602
  • 2
  • 34
  • 42
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'
Indra Prakash Tiwari
  • 947
  • 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