left pad a column with zero

[lhrpa]

I need to load a numeric column into a varchar(12) column, and I need to left pad with zeros. The numeric values could be of varying lengths from 5 - 11.

For example, a 12345 from my input data file needs to look like 000000012345 once loaded into the table column.

Does anyone know a good way to do this in SQL Server 2000?

Thank you!

Linda

 

[SQLSister]

here's an example from Boooks Online:
USE Northwind
GO
DROP TABLE t1
GO
CREATE TABLE t1
(
c1 varchar(3),
c2 char(3)
)
GO
INSERT INTO t1 VALUES ('2', '2')
INSERT INTO t1 VALUES ('37', '37')
INSERT INTO t1 VALUES ('597', '597')
GO
SELECT REPLICATE('0', 3 - DATALENGTH(c1)) + c1 AS [Varchar Column],
REPLICATE('0', 3 - DATALENGTH(c2)) + c2 AS [Char Column]
FROM t1
GO

 

[JohnDTampaBay]

Another option ...

INSERT INTO OutTable
SELECT Right('0000000' + Cast(InColumn as varchar(12)), 12)
FROM InTable


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[lhrpa]

Thanks, you both were a big help! I ended up combining both of your tips,and used this in my insert stmt:

insert into mytable (cola)
select
REPLICATE('0',12-LEN(colb)) + CAST(colb) AS VARCHAR(12)) from inputtable

colb is numeric in my input data.

Thank you again!

Linda