StackOverflow Question #64349366, PostgreSQL

Language: Layout:

CREATE OR REPLACE FUNCTION replaceDescription(description TEXT)
       RETURNS TEXT
       LANGUAGE PLPGSQL
    AS
    $$
    DECLARE 
       pos INT := 1;
       chr CHAR;
       parsing BOOLEAN = FALSE;
    BEGIN
       WHILE pos <= CHAR_LENGTH(description) LOOP
          chr := SUBSTRING(description, pos, 1);
          IF chr = '[' THEN
              parsing := TRUE;
          ELSIF chr = ']' THEN
              parsing := FALSE;
          ELSIF parsing THEN
              IF chr = '1' THEN
                  description := CONCAT(LEFT(description, pos - 1),
                                        'one-',
                                        RIGHT(description, CHAR_LENGTH(description) - pos));
                  pos := pos + 3;
              ELSIF chr = '2' THEN
                  description := CONCAT(LEFT(description, pos - 1),
                                        'two-',
                                        RIGHT(description, CHAR_LENGTH(description) - pos));
                  pos := pos + 3;
              ELSIF chr = '3' THEN
                  description := CONCAT(LEFT(description, pos - 1),
                                        'three-',
                                        RIGHT(description, CHAR_LENGTH(description) - pos));
                  pos := pos + 5;
              ELSIF chr = '4' THEN
                  description := CONCAT(LEFT(description, pos - 1),
                                        'four-',
                                        RIGHT(description, CHAR_LENGTH(description) - pos));
                  pos := pos + 4;
              END IF;
          END IF;
          pos := pos + 1;
       END LOOP;
       
       RETURN REPLACE(description, '-]', ']');
    END;
    $$;

DROP TABLE IF EXISTS some_table;
    CREATE TABLE some_table (description TEXT);
    INSERT INTO some_table (description) VALUES ('Also test that 123 *don''t* get replaced. Lorem ipsum dolor sit amet [123], consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et [234] magna aliqua. Also test that 234 *don''t* get replaced.');
    SELECT replaceDescription(description) FROM some_table;


absolute service time: 0,41 sec