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

Question

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

Why are these intetgers? Are you planning to subtract one telephone number from another? — Strawberry
– Strawberry, Commented Feb 17, 2014 at 13:38
no i just want to insert 92 in front of all numbers in a coloum. this is my requirement — Ali Ahmad
– Ali Ahmad, Commented Feb 17, 2014 at 13:39

Alma Do · Accepted Answer · 2014-02-17 13:41:51Z

2

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

answered Feb 17, 2014 at 13:41

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CodeBird · Accepted Answer · 2014-02-17 13:52:02Z

1

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

edited Feb 17, 2014 at 13:52

answered Feb 17, 2014 at 13:43

CodeBird

3,8132 gold badges22 silver badges38 bronze badges

3 Comments

Alma Do Over a year ago

While this looks good (from operation cost viewpoint) - it requires that all numbers are same length (and also length should be known before UPDATE) - so I think that should be mentioned

CodeBird Over a year ago

I supposed he knows all the lengths and they are all the same as the column is int, and he wants to add 92 to all of the so they are all in the same country or region

Alma Do Over a year ago

Well, even in same country there could be different numeration length (if we're speaking about phone numbers)

Rahul · Accepted Answer · 2014-02-17 13:50:35Z

0

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));

edited Feb 17, 2014 at 13:50

answered Feb 17, 2014 at 13:43

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

Alma Do Over a year ago

You can't rely on implicit conversions in common case (because strict mode could prohibit that)

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related