Format data in a column

I need to format the data in 2 columns in a SQL view. Each column contains
numeric values. They both need to be formatted so that they are padded with
leading zeros if needed. Example: Column 1 is set to a length of 9, so if
the value of 999999 it needs to be formatted as 000999999. Column 2 is the
same way but has a lenght of 15. Can someone help me out with this? ThanksForgot to mention I am using a MS Access 2003 front end and a SQL DB.
"CD" wrote:
> I need to format the data in 2 columns in a SQL view. Each column contain
s
> numeric values. They both need to be formatted so that they are padded wi
th
> leading zeros if needed. Example: Column 1 is set to a length of 9, so i
f
> the value of 999999 it needs to be formatted as 000999999. Column 2 is th
e
> same way but has a lenght of 15. Can someone help me out with this? Thanks[/colo
r]|||Try,
update t1
set
c1 = right(replicate('0', 9) + c1, 9),
c2 = right(replicate('0', 15) + c2, 15)
AMB
"CD" wrote:
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||CD,
While we can write the T-SQL to do this, I'd recommend using Access 2003 to
handle the formatting of the data - more versatile.
HTH
Jerry
"CD" <CD@.discussions.microsoft.com> wrote in message
news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||You can create a user degined function for this:
CREATE FUNCTION fn_0Int
(
@.input as int,
@.CharLength as int
)
returns varchar(255)
AS
begin
return
right(replicate('0',@.CharLength)+convert
(varchar(255),@.input),@.CharLength)
end
GO
print dbo.fn_0Int(999999,9)
000999999
print dbo.fn_0Int(999999,15)
000000000999999
"CD" <CD@.discussions.microsoft.com> wrote in message
news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||Jerry,
I had the same thoughts, but I couldn't find where I could do this.
"Jerry Spivey" wrote:

> CD,
> While we can write the T-SQL to do this, I'd recommend using Access 2003 t
o
> handle the formatting of the data - more versatile.
> HTH
> Jerry
> "CD" <CD@.discussions.microsoft.com> wrote in message
> news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
>
>|||On Wed, 28 Sep 2005 11:55:03 -0700, CD wrote:
>I need to format the data in 2 columns in a SQL view. Each column contains
>numeric values. They both need to be formatted so that they are padded wit
h
>leading zeros if needed. Example: Column 1 is set to a length of 9, so if
>the value of 999999 it needs to be formatted as 000999999. Column 2 is the
>same way but has a lenght of 15. Can someone help me out with this? Thanks[/color
]
CREATE VIEW FormattedData
AS
SELECT RIGHT(REPLICATE('0', 9) + CAST(Column1 AS varchar), 9) AS Column1
, RIGHT(REPLICATE('0',15) + CAST(Column2 AS varchar),15) AS Column2
FROM YourTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)