CODEWITHSUNDEEP
sqlsql-server
Andre
Andre

Reputation: 839

Padding number with 0s before doing a insert into a SQL Server database

I need to insert a value into this database field but it needs to be padded with leading 0s if this number has less than 9 digits, as an example, 12345 should be converted to 000012345 before inserting it into the database. I am trying this, but it's not working, I can not use stored procedure, is it possible?

insert into my_table (right('00000000' + cast(account as char(8)), 8)) values ('12345')

Upvotes: 0

Views: 294

Answers (2)

Anon
Anon

Reputation: 10908

Use STR() to convert numbers to right-justified strings. Procedures that use CONVERT(varchar(8),...) can't be parameterized because varchar(@size) is invalid.

SELECT REPLACE(STR(12345,8),' ','0')

Upvotes: 0

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

Two issues here:

  1. You have the expression (i.e. the CAST and RIGHT functions) in the column name specification area instead of the value area
  2. You are casting to a right-padded text field (i.e. CHAR) of 8 characters (left 5 chars are 12345 and 3 more spaces to the right) and then adding the zeros to the left before grabbing the 8 right-most characters. The right-most 8 just happen to be 12345 + 3 spaces ;-). You should use VARCHAR instead.

Try this:

INSERT INTO my_table (Account)  
VALUES (RIGHT('00000000' + CAST(12345 AS VARCHAR(8)), 8))

Upvotes: 3

Related Questions