appending zeros to integer
-
MY client has a primary key field that uses integers as the primary key identifier.
they want fileds that have less than 2 chars to be appending with a zero.
So for the int field that is 0 that would like it to be 00 or if it is 1 then 01 etc etc.
How can I accomplish this either in a query or withing the table itself??
Thanks
-
This sounds like something that should be done via code or SQL as the data is pulled out of the column. You can set an integer value of 1 to 01, but SQL Server will still store it as 1. Either change the datatype to Char(2) (or varchar) or do the appending at the presentation layer.
-
so how would I do this via query??
i am not sure how to have it only append to the columns where there is only one char?
I don't really want to change the field type.
-
DECLARE @Ints TABLE (value int)
SET NOCOUNT ON
INSERT INTO @Ints
SELECT '1' UNION ALL
SELECT '12'
SELECT CASE LEN(Value) WHEN 1 THEN '0' + CAST(Value as char(2)) ELSE CAST(Value as char(2)) END as 'Value'
FROM @Ints
-
Try;
SELECT CONVERT (CHAR(2), left( '00', 2-len(<column name>
) + CONVERT(CHAR(2), <column name>)FROM ....
Replace <column name> with the name of the column from the table
-
Damn those 'faces"!!!
Try;
SELECT CONVERT (CHAR(2), left( '00', 2-len(<column name>
) + CONVERT(CHAR(2), <column name> )FROM ....
Replace <column name> with the name of the column from the table
-
Double damn, so much for the "Preview" button!!
That should be two right brackets in the code.
-
Sounds like a good argument for a client side solution - perhaps a format on the client side?
-
thanks guys I will check it out I won't be able to do that till monday thanks!!!
-
I have had to do this a several times and have found the RIGHT function to be very effective. For example, for a two character column you could do the following:
SELECT RIGHT('00' + col, 2) FROM sometable
But after reading your post again, you probably will have to use a CASE statement because of the rule if the int has < 2 characters:
SELECT CASE WHEN len(col) < 2 THEN RIGHT('00' + col, 2) ELSE col END FROM sometable
Of course if all values need to have leading zeros for an INT column you could use:
SELECT RIGHT('0000000000' + col, 10) FROM sometable
Hope that helps.
-
Function works:
create
function [dbo].[fn_Padd]
(
@FileIn int
)
returns
varchar(100)
as
begin
declare
@newnum as varchar(100),
@strLen as int,
@pad as int,
@padded as varchar(1)
--think you only want to pad if single digit
if
len(@filein) > 1
begin
return
@FileIn
end
set
@newnum = @FileIn
set
@strlen = len(@newnum)
set
@pad = 100 - @strlen
set
@padded = replicate('0',@pad)
set
@newnum = @padded + @newnum
exit_fn:
return
@NEWNUM
end
-
Are we sure you will always have 99 or less? And where is this being displayed? There are lots of options with application code.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply