Can you rearrange a string in Postgres?

Question

I'm looking for a way to rearrange a string in Postgres. I need to make a string non-recognizable but it also needs to be reversible so a hashing function is out and so is anything that is randomly generated. In addition, I am working with strings that are 10 characters long, so I want to keep the resulting rearranged string in the same ballpark. This isn't a secure string, this is just to keep something form being immediately recognizable.

I'd like to create something along the lines of...

Select rearrange('12345abcde');

that will produce something like this...

'1a2b3c4d5e'

The rearranging function doesn't need to be identical, and if I was pointed in the right direction, I could adapt a function to my specific needs, but the length can vary and since I need to be able to reverse the procedure, it needs to be done in a non-random method.

Yes, you can rearrange strings in postgres! What have you tried so far? — JeffUK
– JeffUK, Commented Mar 4, 2019 at 19:23

klin · Accepted Answer · 2019-03-04 19:54:28Z

2

Example function:

create or replace function rearrange(str text, n int)
returns text language sql immutable as $$
    select string_agg(item, '' order by (ord- 1) % n, ord)
    from regexp_split_to_table(str, '') with ordinality as a(item, ord)
$$;

select rearrange('12345abcde', 5);

 rearrange  
------------
 1a2b3c4d5e
(1 row)

You can reverse the conversion on ten-char strings using 2 as the second argument:

select rearrange(rearrange('12345abcde', 5), 2);

 rearrange  
------------
 12345abcde
(1 row)

answered Mar 4, 2019 at 19:54

klin

123k15 gold badges241 silver badges263 bronze badges

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

2 Comments

John Sly Over a year ago

I didn't specify my postgres version, but it seems 'with ordinality' didn't exist in 9.3. I'm still taking this in, but is there a way to create the same results without using ordinality?

klin Over a year ago

Alternatives exist but are much less elegant. ordinality (and jsonb) are worth upgrading, anyway 9.3 is unsupported already.

Collectives™ on Stack Overflow

Can you rearrange a string in Postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related