SQL Query to concat a string in the database

Question

I have a database table, with a column that contains integers. Each entry is a phone number, and they are all missing a zero at the beginning.

e.g. I have 798514586, 785558999

I want to run a SQL query that will modify each entry by putting a zero infront of it.

So the result will be 0798514586, 0785558999

IS there such a query to do this?

Answer 1

Try this

Syntax:

UPDATE <table> SET <column_to_update> = CONCAT(<string_to_concat>, <column_to_update>)

Example:

UPDATE contacts SET phone = CONCAT('0', phone)

Answer 2

I suppose you dont't want to add leading zero if it already exists:

update TableName
set SomeColumn = concat('0', SomeColumn)
where SomeColumn not like '0%'

Answer 3

It's not a good idea to store phone numbers as INTs, it's better to use a VARCHAR here. I would suggest you do add a new varchar column:

ALTER TABLE numbers ADD COLUMN phone_new VARCHAR(15);

then you can use an UPDATE query:

UPDATE numbers
SET
  phone_new = CONCAT('0', phone)

MySQL will automatically cast the number to a string, and using CONCAT you can add a leading zero.

Answer 4

You can try by this:

update tableName set fieldName = CONCAT('0',fieldName)

Answer 5

You can use LPAD :

Update _table set _col  = LPAD(_col , 10, '0');