Mysql extend the phone number

Question

In mysql database, I have those data as bellow:

id, phonenumber, location
1,  111222, NY
2,  222333, GB
....

Now I would extend the phonenumber field (varchar) for example, if I extend one more number, then the expect data would be:

id, phonenumber, location
1,  1112220, NY
2,  1112221, NY
3,  1112222, NY
4,  1112223, NY
5,  1112224, NY
6,  1112225, NY
7,  1112226, NY
8,  1112227, NY
9,  1112228, NY
10, 1112229, NY
11, 2223330, PH
12, 2223331, PH
...

And this is the example for add one more number. The real situation is I would need to add 4 more number. (add suffix from 0000 ~9999 )

Any fast way to do this via running few sql command ? Thanks!

Could possibly use [this](http://stackoverflow.com/questions/6069024/syntax-of-for-loop-in-sql-server). Never done it before, so not 100% sure. — Sumurai8, Aug 31 '14 at 14:13

score 0 · Answer 1 · answered Aug 31 '14 at 14:14

0

You can use union query and cross join with your table which holds 2 phone numbers

select *, concat(phonenumber,n) new_phonenumber from t
cross join
( select 0 as n
 union
select 1 as n
 union
select 2 as n
 union
select 3 as n
 union
select 4 as n
 union
select 5 as n
 union
select 6 as n
 union
select 7 as n
 union
select 8 as n
 union
select 9 as n

) t1

order by phonenumber, n

Sample demo

answered Aug 31 '14 at 14:14

M Khalid Junaid

63,861
10
90
118

thanks,but this only works for **1** more number. I have no idea on how to change this SQL to **4** digital number situation. – Niuya Aug 31 '14 at 14:30
@Niuya for four numbers you can use Gordon's here is the working [*`fiddle demo`*](http://sqlfiddle.com/#!2/e1069/8) – M Khalid Junaid Aug 31 '14 at 14:46

Gordon Linoff · Accepted Answer · 2014-08-31T14:51:19.317

You can do this, although it is a bit of a pain in MySQL. You need to generate the values for extending The following does this:

select concat(n1.n, n2.n, n3.n, n4.n)
from (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
      select 6 union all select 7 union all select 8 union all select 9 union all select 0
     ) n1 cross join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
      select 6 union all select 7 union all select 8 union all select 9 union all select 0
     ) n2 cross join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
      select 6 union all select 7 union all select 8 union all select 9 union all select 0
     ) n3 cross join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
      select 6 union all select 7 union all select 8 union all select 9 union all select 0
     ) n4;

You can combine that with your query using cross join to get the data you want:

select id, concat(phonenumber, n1.n, n2.n, n3.n, n4.n) as phonenumber, location
from yourtable t cross join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
        select 6 union all select 7 union all select 8 union all select 9 union all select 0
       ) n1 cross join
       (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
        select 6 union all select 7 union all select 8 union all select 9 union all select 0
       ) n2 cross join
       (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
        select 6 union all select 7 union all select 8 union all select 9 union all select 0
       ) n3 cross join
       (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
        select 6 union all select 7 union all select 8 union all select 9 union all select 0
       ) n4;

If you want these results in a table, I would suggest you save this to another table using:

create table as . . .

before the query.

Here is a SQL Fiddle.

There is a ````.```` instead of a ````,```` in your second query (````concat(phonenumber, n1.n, n2.n. n3.n, n4.n)````). Would edit it, but it's not 6 chars, otherwise a very nice answer. — Alex Szabo, Aug 31 '14 at 14:30
Still get error when running this sql. But I think it should be small issue need a small correction , I am still trying... — Niuya, Aug 31 '14 at 14:37
I found the result table have such suffix "10111" , that would be out of range (0000~9999) . — Niuya, Aug 31 '14 at 15:05
@Niuya . . . That was a typo on my part. The last value in the subqueries was 10 and no 0. — Gordon Linoff, Aug 31 '14 at 15:11
@GordonLinoff Thanks! I didn't notice that part. It works now! — Niuya, Aug 31 '14 at 15:36
Just one more question, @GordonLinoff , I prefer to pick few rows to run (to add suffix ) per time. Where should I put `limit 10` (for example) in your SQL command ? — Niuya, Aug 31 '14 at 15:40
@Niuya . . . Essentially you want to replace `mytable` with `(select t.* from mytable t order by id limit 10)`. — Gordon Linoff, Aug 31 '14 at 15:43

score 0 · Answer 3 · answered Aug 31 '14 at 14:23

0

Please try this code
<?php
$con=mysql_connect("localhost","username","password");
$db=mysql_select_db("databasename");


$query="select * from table_name";
$result=mysql_query($query);
while($re=mysql_fetch_array($result))
{
echo $re[1];
}
?>

answered Aug 31 '14 at 14:23

Ankit

13
5

2

Please add some clarification to your code - why would it help, what it does, etc. to make it a good answer. – Ilya Luzyanin Aug 31 '14 at 14:52

score 0 · Answer 4 · answered Aug 31 '14 at 14:58

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT i4.i * 1000 + i3.i * 100 + i2.i*10 +i1.i j 
  FROM ints i1,ints i2, ints i3, ints i4 
 ORDER 
    BY j DESC
 LIMIT 1;
+------+
| j    |
+------+
| 9999 |
+------+

Mysql extend the phone number

4 Answers4