CODEWITHSUNDEEP
sqlsql-servert-sql
D26
D26

Reputation: 49

add leading zeros to a varchar column

Select len(productid),productid,*
 FROM dbo.produts
   where Place = 'KA'
   and len(productid) <> 10
   order by len(productid)

this query filters the data that needs to be updated -- i need to update the data in 'productid' that is not in the correct format --(the data should be 10 characters, in 4-2-4 format with leading 0s where they are needed) (productid column is a varchar (256) column) basically i need to add leading zeros to a varchar column that has a where condition 

|productid| |Correct productid value| |
---------------------------------------
|234-55-43||000234-55-43|

|76-7-89||0000076-7-89|

what are the possible solutions for updating these records?

Upvotes: 3

Views: 6093

Answers (2)

SqlZim
SqlZim

Reputation: 38023

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 '____-__-____'

Upvotes: 1

Lamak
Lamak

Reputation: 70638

This is very simple, actually:

SELECT productid,
       RIGHT('000000000'+productid,10) CorrectProductid
FROM dbo.YourTable
;

Upvotes: 3

Related Questions