CODEWITHSUNDEEP
mysqlsql
Skyliquid
Skyliquid

Reputation: 392

mysql command - Update if char length

I have a table that contains a column 'pin' which is varchar(3). I made a mistake and changed 'pin' to int(3) and now all the pins that started with char '0' is removed.

Now i got it back to varchar but I need to do a query that will check if pin length is less than 3 then add 0 or 00 in the first char(s) depending on the length of the value.

more like : if(value.length == 2){add '0' on the left}

I am not a pro when it come to sql queries. any ideas ?

Upvotes: 0

Views: 1178

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You are looking for lpad():

update table t
    set pin = lpad(pin, 3, '0')
    where length(pin) < 3;

Upvotes: 4

Related Questions