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?

I've just checked: PostgreSQL only allows a maximum of 9 capturing groups, so you can't use a regex for this, it seems. — Tim Pietzcker
– Tim Pietzcker, Commented Jan 8, 2010 at 11:19
You have tagged your question as both mysql and postgresql. Please indicate which database you are actually using or if you require a solution for both. — mikej
– mikej, Commented Jan 8, 2010 at 12:52
I will use any database which will give me result. It can be mysql postrgesql or oracle. — DImitris N.
– DImitris N., Commented Jan 8, 2010 at 12:58
MySQL doesn't have much in the way of regex support at all. And Oracle has the same limitation as PostgreSQL. — Tim Pietzcker
– Tim Pietzcker, Commented Jan 8, 2010 at 13:05

score 2 · Accepted Answer · 2010-01-08 13:59:01Z

2

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)

edited Jan 8, 2010 at 13:59

answered Jan 8, 2010 at 13:39

user80168

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

Comments

Tomalak · Accepted Answer · 2010-01-08 12:45:55Z

1

reverse the string by conventional means (i.e., a built-in string function)
regex-replace (\d) with $1., globally
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.

answered Jan 8, 2010 at 12:45

Tomalak

339k68 gold badges547 silver badges635 bronze badges

2 Comments

Amarghosh Over a year ago

Just in case it is supported: replace (\d)(?=\d) with $1.

Tim Pietzcker Over a year ago

@Amarghosh: PostgreSQL is the only one that supports lookahead, so that should work. Lookbehind is not supported, though.

Magnus Hagander · Accepted Answer · 2010-01-08 13:21:42Z

0

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(...)

answered Jan 8, 2010 at 13:21

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

2 Comments

user80168 Over a year ago

He also wants to reverse it - I missed it as well :(

Magnus Hagander Over a year ago

Gah. I have reverse() in there for that, forgot that this is a custom function I've put in my local database :S Why don't we have reverse()?

Community · Accepted Answer · 2017-05-23 10:24:37Z

0

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.')

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Jan 8, 2010 at 13:08

mikej

66.6k18 gold badges156 silver badges131 bronze badges

3 Comments

DImitris N. Over a year ago

Mysql also working for this with you solution, but results are strange if there is more than 10 digits: 1.1..1.1..1.1..1.1..1.1..1. 2.2.2.2.2.2.2.2.2.2.2. Looks like it understands well only untill 9 references, is there any way to use more?

mikej Over a year ago

It seems to work OK for me with longer values e.g. select replace(replace(replace(reverse('11111111111222222222211111111'), '3', '3.'), '2', '2.'), '1', '1.'); gives "1.1.1.1.1.1.1.1.2.2.2.2.2.2.2.2.2.2.1.1.1.1.1.1.1.1.1.1.1." - What is the value that you're trying it with?

DImitris N. Over a year ago

Yes it's working very nice. I didn't understood clearly how it's working, and used something like ....(REPLACE(REVERSE(number), '12', '12.'), '11', '11.'),..... because of 2 digit, I had strange result. But now it's ok. Thank you!

DImitris N. · Accepted Answer · 2010-01-08 14:01:21Z

0

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!!

answered Jan 8, 2010 at 14:01

DImitris N.

11 silver badge1 bronze badge

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

2 Comments

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related