David David - 1 month ago 25
SQL Question

Group phone numbers with SQL or PHP

I have many phone numbers like this:

030/942931862
03361/23176635


I want to group them like this:

0 30/9 42 93 18 62
0 33 61/23 17 66 35


That means I want to have a space after two digits from the end. But that should "start again" at the
/
. Means
0 33 61/23 17 66 35
instead of
03 36 1/ 23 17 66 35
.

Is there a way to do this with SQL in my MySQL database? Otherwise: Does anybody know a PHP solution?

Answer

Ugly as hell but working. I assumed that there is always a slash and that no token (from each side of the slash) is longer than 10 characters.

select      concat_ws('/',ltrim (concat_ws (' ',substr(concat(space(10),substring_index(phone_number,'/', 1)),-10,2),substr(concat(space(10),substring_index(phone_number,'/', 1)),-8,2),substr(concat(space(10),substring_index(phone_number,'/', 1)),-6,2),substr(concat(space(10),substring_index(phone_number,'/', 1)),-4,2),substr(concat(space(10),substring_index(phone_number,'/', 1)),-2,2))),ltrim (concat_ws (' ',substr(concat(space(10),substring_index(phone_number,'/',-1)),-10,2),substr(concat(space(10),substring_index(phone_number,'/',-1)),-8,2),substr(concat(space(10),substring_index(phone_number,'/',-1)),-6,2),substr(concat(space(10),substring_index(phone_number,'/',-1)),-4,2),substr(concat(space(10),substring_index(phone_number,'/',-1)),-2,2))))

from        t
;