CODEWITHSUNDEEP
mysqlsqlconcatenation
Ali Ahmad
Ali Ahmad

Reputation: 1055

MySQL update query to prepend digit to existing column with integers values

I have a mysql database column named like telephoneNo

Telephone No 
25645656
45454545
45565656

I want to prepend two digits XX to every value of telephoneNo column

Telephone No 
xx25645656
xx45454545
xx45565656

I was trying to workout with concat but its not with integer values in my case please help with update query

Upvotes: 1

Views: 2779

Answers (3)

CodeBird
CodeBird

Reputation: 3858

That's a hack change your col to varchar or something.

UPDATE table SET telephoneNo=9200000000+telephoneNo;

EDIT:

This method requires that all your numbers are of the same length, and 8 digits long, if all your numbers are more or less than 8 digits modify the number of 0's after the 92 accordingly

Upvotes: 1

Rahul
Rahul

Reputation: 77896

If your telephone column is int, you can use concat string function to concat with int or string even like below

SELECT CONCAT(xx, telephone);

(OR)

SELECT CONCAT('xx', telephone);

(OR)

Explicitly cast your int column value

SELECT CONCAT('xx', CAST(telephone AS CHAR));

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

You can use CAST() to convert your integers explicitely:

UPDATE t SET phone=CAST(CONCAT('10', phone) AS UNSIGNED)

That will work with integer prefixes. However, I don't see solid reason to store phone numbers as integers and not strings

Upvotes: 2

Related Questions