regexp_replace in database to reverse a numeric string and insert a period between each digit

Question

I have database where one column contains numbers. 3 example values:

1111111555
2222222555
3333333555

which I need to reverse and put a dot between each digit. i.e. the result for each of the examples above would be:

5.5.5.1.1.1.1.1.1.1
5.5.5.2.2.2.2.2.2.2
5.5.5.3.3.3.3.3.3.3

respectively.

I then need to update another column with the result. How can I make such replacement?

An online regexp test is giving me the result I need with this:

(\S)(\S)(\S)(\S)(\S)(\S)(\S)(\S)(\S)(\S)/g$10.$9.$8.$7.$6.$5.$4.$3.$2.$1

but I can't get it to successfully update the database. Any ideas how make it work?

Answer 1

1. reverse the string by conventional means (i.e., a built-in string function)
2. regex-replace (\d) with $1., globally
3. remove the last dot from the result

More complex regexes could save you the last step, for example by means of look-ahead. I'm not sure what your DBMS of choice supports here, so I kept it simple.

Answer 2

In Oracle, assuming your starting value is in a column called f you could do:

substr(regexp_replace(reverse(f), '(\d)', '\1.'), 0, (length(f) * 2) - 1)

This is using the approach outlined by Tomalek

Of course you could hard code 19 in place of (length(f) * 2) - 1 if you know that the original value is always going to be exactly 10 digits.

MySQL has a function for doing regexp matching but doesn't support regexp substitution so you would need to do nested replacement of each digit 0-9 in turn. e.g. for 1-3:

replace(replace(replace(reverse(f), '3', '3.'), '2', '2.'), '1', '1.')

Answer 3

Something like:

SELECT regexp_replace(reverse(yourcol), E'(\\S)(?=\\S)', E'\\1.', 'g')

should work. And to update, of course

UPDATE yourtable SET yourcol=regexp_replace(...)

Answer 4

Correct answer for PostgreSQL:

CREATE OR REPLACE FUNCTION reverse_array(ANYARRAY) RETURNS ANYARRAY AS $$
    SELECT array( (SELECT $1[i] FROM generate_series( array_upper($1, 1), array_lower($1, 1), -1 ) i ) );
$$ language sql;

# select array_to_string(
    reverse_array(
        regexp_split_to_array( i, '' )
    ), '.'
  )
  from (values ('1111111555'), ('2222222555'), ('3333333555')) x (i);
   array_to_string
---------------------
 5.5.5.1.1.1.1.1.1.1
 5.5.5.2.2.2.2.2.2.2
 5.5.5.3.3.3.3.3.3.3
(3 rows)

Answer 5

I'm trying oracle: SELECT REGEXP_REPLACE(REVERSE(phone),'(\d)','\1.') from records

4.3.2.1.1.1.1.1.1.1.1.1.

This working fine for me, even last dot is useful, I'm going to attach there e164.arpa with concat. But this is next question.

Thanks alot all of you!!