November 18, 2008 at 10:58 am
The field data type is INT.
I want to display it always as "01","02","03" format.
What is the best way to do it?
November 18, 2008 at 11:01 am
riga1966 (11/18/2008)
The field is CHAR(2)Users enter "1", "01","4","04"
I want to display it always as "01","02","03" format.
What is the best way to do it?
declare @STR char(2);
set @STR = '1';
select right('0' + ltrim(rtrim(@str)), 2);
set @STR = ' 1';
select right('0' + ltrim(rtrim(@str)), 2);
set @STR = '10'
select right('0' + ltrim(rtrim(@str)), 2);
Does this help you out?
November 18, 2008 at 11:01 am
I would probably write an INSERT/UPDATE trigger that would update the field to '0' + field if LEN(field) = 1.
November 18, 2008 at 11:12 am
Thank you Lynn.
So
right('0' + ltrim(rtrim(@str)), 2)
only adds leading 0 in case LEN is less than 2?
I'm just trying to understand the mechanism...
November 18, 2008 at 11:35 am
riga1966 (11/18/2008)
Thank you Lynn.So
right('0' + ltrim(rtrim(@str)), 2)
only adds leading 0 in case LEN is less than 2?
I'm just trying to understand the mechanism...
Actually, it is always adding a leading '0' to the string, we are just taking the right most 2 characters. If @STR (or the column in your table) has a single character value, such as a '1', then you end up with '01'.
November 18, 2008 at 11:55 am
I guess you can try this way also.
SELECT
CASE
WHEN LEN(COLUMNNAME) <>2 then '0'+ COLUMNNAME
ELSE COLUMNNAME
END
FROM TABLENAME
Hope this helps.
November 18, 2008 at 12:35 pm
sqlizer,
Your code does not give the right results if the column is INTEGER.
Lynn's code works perfectly.
November 18, 2008 at 12:45 pm
The field is CHAR(2)
Users enter "1", "01","4","04"
I want to display it always as "01","02","03" format.
What is the best way to do it?
You mentioned in the beginning that it's CHAR(2) column.
Check this out. I created table with 2 columns. 1st column has CHAR(2) datatype and 2nd column has INT data type. Inserted just 5 rows and then run query i have mentioned in the last and I guess this time you will get your result.
CREATE TABLE Test
(Col1 char(2),
Col2 int)
GO
INSERT INTO Test
SELECT '1', 1
UNION ALL
SELECT '02', 2
UNION ALL
SELECT '2', 02
UNION ALL
SELECT '3', 3
UNION ALL
SELECT '03', 03
GO
SELECT
CASE
WHEN LEN(Col1) <>2 then '0'+ Col1
ELSE Col1
END ,
'0' + CAST(Col2 AS VARCHAR(2))
FROM Test
November 18, 2008 at 12:55 pm
This type of question is asked frequently and I always see similar responses. The problem is this solves a single instance. Therefore, the next time this occurs, the developer or DBA retypes the code (aka reinvents the wheel). Rather, try creating a UDF that does the same thing then reuse the UDF. See below...
create function dbo.formati(@value int, @pad int, @padchar char(1) )
returns varchar(max)
as
begin
-- declare @v varchar(@pad)
return (select REPLICATE( @padchar ,@pad-len(@value)) + convert(varchar,@value) )
-- return @v
end
go
create function dbo.formatd(@value decimal(10,2), @pad int, @padchar char(1) )
returns varchar(max)
as
begin
-- declare @v varchar(@pad)
return (select REPLICATE( @padchar ,@pad-len(@value)) + convert(varchar,@value) )
-- return @v
end
go
select dbo.formati( MONTH('2008-09-01'),2, '0' )
select dbo.formati( 123,5, '0' )
select dbo.formatd( 123.45,8, '-' )
declare @d decimal(10,2)
select @d=9
select LEN(@d)
As you can see, not only does it pad both int and decimal types, you can also change the pad character.
DAB
November 18, 2008 at 12:56 pm
Sorry.
My fault.
The column is INT.
Not CHAR(2)
November 18, 2008 at 2:03 pm
SQLServerLIfer,
Thank you so much for your input.
Looks like a comprehensive solution.
I'll read about this REPLICATE thing more.
I'm not familiar with it.
November 18, 2008 at 8:04 pm
riga1966 (11/18/2008)
The field data type is INT.I want to display it always as "01","02","03" format.
What is the best way to do it?
Now that you have a couple of answers, please tell us WHY you want to do this and why you think it should be done in the database instead of in the GUI. There may be an even better answer available depending on what you are actually doing this formatting for.
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply