Множественная функция REPLACE в Oracle

Ответ 1

Даже если этот поток старый, он первый в Google, поэтому я буду размещать эквивалент Oracle для функции, реализованной здесь, используя регулярные выражения.

Является довольно быстрым, чем вложенная функция replace(), и намного чище.

Чтобы заменить строки "a", "b", "c" на "d" в столбце строки из данной таблицы

select regexp_replace(string_col,'a|b|c','d') from given_table

Это не что иное, как регулярное выражение для нескольких статических шаблонов с оператором "или".

Остерегайтесь специальных символов регулярного выражения!

Ответ 2

Принятый ответ как заменить несколько строк в Oracle предполагает использование вложенных операторов REPLACE, и я не думаю, что есть лучший способ.

Если вы собираетесь использовать это, вы можете рассмотреть возможность написания своей собственной функции:

CREATE TYPE t_text IS TABLE OF VARCHAR2(256);

CREATE FUNCTION multiple_replace(
  in_text IN VARCHAR2, in_old IN t_text, in_new IN t_text
)
  RETURN VARCHAR2
AS
  v_result VARCHAR2(32767);
BEGIN
  IF( in_old.COUNT <> in_new.COUNT ) THEN
    RETURN in_text;
  END IF;
  v_result := in_text;
  FOR i IN 1 .. in_old.COUNT LOOP
    v_result := REPLACE( v_result, in_old(i), in_new(i) );
  END LOOP;
  RETURN v_result;
END;

а затем используйте его следующим образом:

SELECT multiple_replace( 'This is #VAL1# with some #VAL2# to #VAL3#',
                         NEW t_text( '#VAL1#', '#VAL2#', '#VAL3#' ),
                         NEW t_text( 'text', 'tokens', 'replace' )
                       )
FROM dual

Это текст с некоторыми токенами для замены

Если все ваши токены имеют одинаковый формат ('#VAL' || i || '#'), вы можете опустить параметр in_old и вместо этого использовать свой счетчик циклов.

Ответ 3

Помните о последствиях

SELECT REPLACE(REPLACE('TEST123','123','456'),'45','89') FROM DUAL;

заменит 123 на 456, а затем найдет, что он может заменить 45 на 89. Для функции, которая имела эквивалентный результат, ей пришлось бы дублировать приоритет (т.е. заменять строки в том же порядке).

Аналогично, взяв строку "ABCDEF" и указав ей заменить "ABC" на "123" и "CDE" на "xyz", все равно придется учитывать приоритет, чтобы определить, было ли оно "123EF" или ABxyzF".

Короче говоря, было бы сложно найти что-либо общее, что было бы проще, чем вложенный REPLACE (хотя бы что-то, что было скорее функцией стиля sprintf, было бы полезным дополнением).

Ответ 4

В случае, если все ваши исходные и восстановительные строки имеют длину всего один символ, вы можете просто использовать функцию TRANSLATE:

  SELECT translate('THIS IS UPPERCASE', 'THISUP', 'thisup') 
  FROM DUAL

Подробнее см. документацию Oracle.

Ответ 5

Это старый пост, но я в конечном итоге использовал мысли Петра Ланга и сделал аналогичный, но все же другой подход. Вот что я сделал:

CREATE OR REPLACE FUNCTION multi_replace(
                        pString IN VARCHAR2
                        ,pReplacePattern IN VARCHAR2
) RETURN VARCHAR2 IS
    iCount  INTEGER;
    vResult VARCHAR2(1000);
    vRule   VARCHAR2(100);
    vOldStr VARCHAR2(50);
    vNewStr VARCHAR2(50);
BEGIN
    iCount := 0;
    vResult := pString;
    LOOP
        iCount := iCount + 1;

        -- Step # 1: Pick out the replacement rules
        vRule := REGEXP_SUBSTR(pReplacePattern, '[^/]+', 1, iCount);

        -- Step # 2: Pick out the old and new string from the rule
        vOldStr := REGEXP_SUBSTR(vRule, '[^=]+', 1, 1);
        vNewStr := REGEXP_SUBSTR(vRule, '[^=]+', 1, 2);

        -- Step # 3: Do the replacement
        vResult := REPLACE(vResult, vOldStr, vNewStr);

        EXIT WHEN vRule IS NULL;
    END LOOP;

    RETURN vResult;
END multi_replace;

Тогда я могу использовать его следующим образом:

SELECT  multi_replace(
                        'This is a test string with a #, a $ character, and finally a & character'
                        ,'#=%23/$=%24/&=%25'
        )
FROM dual

Это делает так, что я могу использовать любой символ/строку с любым символом/строкой.

Я написал сообщение об этом в своем блоге.

Ответ 6

Я создал общую переменную, заменяющую функцию Oracle Oracle таблицей varchar2 в качестве параметра. Varchar будет заменен на значение позиции rownum таблицы.

Например:

Text: Hello {0}, this is a {2} for {1}
Parameters: TABLE('world','all','message')

Возврат:

Hello world, this is a message for all.

Вы должны создать тип:

CREATE OR REPLACE TYPE "TBL_VARCHAR2" IS TABLE OF VARCHAR2(250);

Функциональность:

CREATE OR REPLACE FUNCTION FN_REPLACETEXT(
    pText IN VARCHAR2, 
    pPar IN TBL_VARCHAR2
) RETURN VARCHAR2
IS
    vText VARCHAR2(32767);
    vPos INT;
    vValue VARCHAR2(250);

    CURSOR cuParameter(POS INT) IS
    SELECT VAL
        FROM
            (
            SELECT VAL, ROWNUM AS RN 
            FROM (
                  SELECT COLUMN_VALUE VAL
                  FROM TABLE(pPar)
                  )
            )
        WHERE RN=POS+1;
BEGIN
    vText := pText;
    FOR i IN 1..REGEXP_COUNT(pText, '[{][0-9]+[}]') LOOP
        vPos := TO_NUMBER(SUBSTR(REGEXP_SUBSTR(pText, '[{][0-9]+[}]',1,i),2, LENGTH(REGEXP_SUBSTR(pText, '[{][0-9]+[}]',1,i)) - 2));

        OPEN cuParameter(vPos);
        FETCH cuParameter INTO vValue;
        IF cuParameter%FOUND THEN
            vText := REPLACE(vText, REGEXP_SUBSTR(pText, '[{][0-9]+[}]',1,i), vValue);
        END IF;
        CLOSE cuParameter;
    END LOOP;

    RETURN vText;

EXCEPTION
      WHEN OTHERS
      THEN
         RETURN pText;
END FN_REPLACETEXT;
/

Использование:

TEXT_RETURNED := FN_REPLACETEXT('Hello {0}, this is a {2} for {1}', TBL_VARCHAR2('world','all','message'));