Reputation: 119

SQL Query to concat a string in the database

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?

Upvotes: 0

Answers (5)

Reputation: 302

You can use LPAD :

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

Upvotes: 0

Reputation: 87

You can try by this:

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

Upvotes: 0

Reputation: 49089

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.

Upvotes: 1

Reputation: 35790

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%'

Upvotes: 1

Reputation: 3946

Try this

Syntax:

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

Example:

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

Upvotes: 2