Please help to implement the procedure with a circular shift of letters and numbers in the text line on the principle n + 1: for example, ABCD01234AB -> BCDE1234BC. All lines in a text field of different length, different case, punctuation marks (dots, dashes, spaces) can occur. Values ​​in the field can also be zero.

Just in case, an additional question is how to apply this procedure inside UPDATE SET?

It is necessary to have something like this:

Create procedure text_change(n varchar(1000)) declare @i char declare @l varchar(100 char) begin For each @l in n For each @i in @l loop @i = ???? end loop; return @l; end; UPDATE название_таблицы SET название_поля = text_change(поле)
  • one
    And how did you get the BCDE1234BC, are they of different lengths with the original string? Is the n field a string or an array of strings? - 0xdb
  • I am sorry, I accidentally made a mistake. It's just a line - Maxim Ermolaev

2 answers 2

In for this there is a translate function.

 UPDATE название_таблицы SET название_поля = translate(название_поля, 'ABCDEF...<остальной алфавит>', 'BCDEFG...<остальной алфавит со смещением 1>');

If you need to be able to set the offset manually (each time is different, for example):

 UPDATE название_таблицы SET название_поля = translate(название_поля, 'ABCDEF...<остальной алфавит>', substr('ABCDEF...<остальной алфавит>', 14) || substr('ABCDEF...<остальной алфавит>', 1, 13));

Here you will have an offset of 13 (the offset must be less than the length of the alphabet).

    For example, for N + 13, the so-called. ROT13

    Get some:

     create or replace function pg_temp.rot13( string text ) returns text as $rot13$ begin return translate( string, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', 'NOPQRSTUVWXYZABCDEFGHIJKLMnopqrstuvwxyzabcdefghijklm' ); end $rot13$ language plpgsql;

    sqlite:

     .load rot13 SELECT rot13('grfg fgevat');