Add Leading Zero to Beginning of a String with Certain Criteria in SQL

Question

I am very new to SQL and I need help trying to figure something out for work.

So I have a table called Acxiom and one of the fields is called Account_ID. Account ID's are all unique but they either start with 07 or 10 and are supposed to be 9 characters long. For some reason, the ERP system that we pull data from does not include the leading zero in its warehouse. I need to link the Acxiom table to another table with Account ID acting as the primary key. I can't do that unless I add the leading zero.

Right now is an example of what the table looks like:

I need it to look like this:

I genuinely appreciate everyone's help!

possible duplicate of [Pad a string with leading zeros so its 3 characters long in SQL Server 2008](http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) — Hart CO, Sep 17 '15 at 14:46

score 0 · Answer 1 · answered Sep 17 '15 at 14:28

DECLARE @pad INT = 9;

SELECT RIGHT(REPLICATE('0', @pad) + Account_ID, @pad)
FROM Acxiom;

or if column is number type then cast:

DECLARE @pad INT = 9;

SELECT RIGHT(REPLICATE('0', @pad) + CAST(Account_ID AS NVARCHAR(10)), @pad)
FROM Acxiom;

or without replicate:

SELECT RIGHT('000000000' + Account_ID, 9)
FROM Acxiom;

Add Leading Zero to Beginning of a String with Certain Criteria in SQL

1 Answers1