Converting int to char with 0 Fill
-
How can I convert an int to a 6 character string and fill the front with zeroes? e.g. 748 becomes '000748'. Thank you.
-
declare @num int,
@charstr char(6)
set @num = 748
set @charstr ='000' + convert(char,@num)
print @charstr
If there is a possibility of a large number use the len function to determine if you will cut the number short.....
Andy. -
Yes, if I use replicate I can cope with other numbers. I did try something similar with cast but that didn't work.
Thank you.
-
This might help...
DECLARE @Int INTEGER
DECLARE @Var CHAR(6)
DECLARE @Pad VARCHAR(6)
SET @Int = 12
SET @Pad = REPLICATE(0, (6 - LEN(@Int)))
SET @Var = CAST(@Pad AS VARCHAR) + CAST(@Int AS VARCHAR)
PRINT '''' + @Var + ''''
Good Hunting!AJ Ahrens
webmaster@kritter.net -
SELECT REPLACE(STR(748,6,0),' ','0')
Far away is close at hand in the images of elsewhere.
Anon. -
To add to the diversity, here's another way.
create table #x ( number int not null )
insert #x values (1)
insert #x values (12)
insert #x values (123)
insert #x values (1234)
insert #x values (12345)
insert #x values (123456)
selectright('000000' + cast(number as varchar(6)), 6)
from #x
drop table #x
go
------
000001
000012
000123
001234
012345
123456
(6 row(s) affected)
/Kenneth
-
Well, there's obviously more than one way to skin a cat (sorry cat lovers , like me); AJ was thinking along the lines I was, but Dave's must get first prize for succinctness; thanks Ken for your too which I'm surprised works but it does! You don't know how long I spent this morning trying to figure this one out.. Anyway, in case you ever need to add a check digit to a 1-6 digit number here's my function code in full:
ALTER FUNCTION WithCheckDigit (@id int)
RETURNS char(7)
AS
BEGIN
DECLARE @MEntry as char(6)
declare @idstring as char(6)
declare @MTotal as int
declare @x as int
declare @WCD as char(7)
declare @MCheckDigit as int
declare @MRemainder as int
SELECT @idstring = ltrim(convert(char(6),@id))
-- original SELECT @MEntry = replicate('0',6-len(@id)) + convert(char(6),@id)
SELECT @MEntry = REPLACE(STR(@id,6,0),' ','0')
select @x = 1
select @MTotal = 0
WHILE @x < 7
begin
SELECT @MTotal = @MTotal + cast(substring(@MEntry, @x, 1) as int) * (8 - @x)
SELECT @x = @x+1
end
SELECT @MRemainder = @MTotal - cast(@MTotal/11 as int) * 11
SELECT @MCheckDigit = 11 - @MRemainder
if @MRemainder = 1
SELECT @WCD = @idstring + 'X'
if @MRemainder = 0
SELECT @WCD = @idstring + '0'
if @MRemainder > 1
SELECT @WCD = @idstring + convert(char(1),@MCheckDigit)
RETURN @WCD
END
-
Gentleman,
How about using the RIGHT() function.
IE...
DECLARE @Number int
SET @Number = 768
SELECT RIGHT('000000' + CONVERT(varchar(6), @Number), 6)
results: 000768
-
DAV,
your solution is remarkably similar to that of Kenneth :-)) But I agree, this is what I prefer, too. I need such conversion quite often, and in the beginning I was using LEN - but then once it occurred to me that RIGHT is all you need to get everything right
-
I dunno... David Burrows' solution doesn't use CONVERT or any form of overt concatenization (+) and may be faster as a result. In the beginning, I used LEN, then graduated to RIGHT with all the other stuff that goes with it like most everyone has. But David's method has the potential for a lot more flexibility for both integers and decimal numbers. He's certainly converted me... and for whole numbers, even his can be shorted up if you trust defaults (wait a minute, trust Microsoft defaults? Did I just say that? Where's the soap?)
SELECT REPLACE(STR(@MyNum,6),' ','0')
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply