Sql How to Add a Leading 0

Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

If the field is already a string, this will work

 SELECT RIGHT('000'+ISNULL(field,''),3)

If you want nulls to show as '000'

It might be an integer -- then you would want

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

As required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. eg '0000' and VARCHAR(4)),4

Add leading zeros to a varchar field

A function that will work for more situations would be REPLICATE. It concatenates a value X amount of times to a string.

SELECT REPLICATE('0', 8-LEN(birthdate_new)) + birthdate_new AS 8_len_birthdate 

This will take the length of your birthdate, subtract it from 8, then put that many leading 0's on the front to make it 8 chars.

How to add leading zero when number is less than 10?

format(number,'00')

Version >= 2012

add leading zeros to a varchar column

If you wanted the corrected format to be in the 4-2-4 format you mentioned:

Using parsename() to parse out the pieces of the string, and right() to add the extra '0's.

select 
    col
  , Corrected = right('0000'+parsename(replace(col,'-','.'),3),4)
  + '-' + right('00'+parsename(replace(col,'-','.'),2),2)
  + '-' + right('0000'+parsename(replace(col,'-','.'),1),4)
from t
where col not like '____-__-____'

rextester demo: http://rextester.com/OXM28014

returns:

+-----------+--------------+
|    col    |  Corrected   |
+-----------+--------------+
| 234-55-43 | 0234-55-0043 |
| 76-7-89   | 0076-07-0089 |
+-----------+--------------+

---

As an update:

update t
  set col = right('0000'+parsename(replace(col,'-','.'),3),4)
            + '-' + right('00'+parsename(replace(col,'-','.'),2),2)
            + '-' + right('0000'+parsename(replace(col,'-','.'),1),4)
where col not like '____-__-____'

SQL how to add a leading 0

LPAD should do the job here:

SELECT LPAD ('123', 5, '0');
00123

SELECT LPAD ('12345', 5, '0');
12345

Try:

UPDATE `it_asset_register`.`tab_id_master` 
 SET tab_id_master.ID = LPAD (tab_id_master.ID, 5, '0')
 WHERE ID = '8407' AND LENGTH(tab_id_master.ID)<5 AND LENGTH(tab_id_master.ID)>0;

---

Related Topics