Обрезать пробелы (строка новой строки и табуляции) в строке в Oracle
Мне нужно обрезать новую строку (Chr(13) и Chr(10) и табуляции от начала и конца строки) в запросе Oracle. Я узнал, что в Oracle нет простого способа обрезать несколько символов. Функция "Trim" обрезает только один символ. Было бы снижение производительности, если бы я вызывал функцию триммера рекурсивно в цикле, используя функцию. Я слышал, что regexp_replace может сопоставлять пробелы и удалять их. Можете ли вы рассказать о надежном способе использования regexp_replace для обрезки нескольких пространств табуляции или новых строк или их комбинаций в начале и конце строки. Если есть какой-либо другой способ, пожалуйста, направьте меня.
16 ответов
Как насчет быстрого и грязного перевода?
Это удалит все вхождения каждого символа в string1:
SELECT translate(
translate(
translate(string1, CHR(10), '')
, CHR(13), '')
, CHR(09), '') as massaged
FROM BLAH;
Regexp_replace является опцией, но вы можете увидеть снижение производительности в зависимости от сложности вашего выражения.
Вот как я бы это реализовал:
REGEXP_REPLACE(text,'(^[[:space:]]*|[[:space:]]*$)')
Если у вас Oracle 10g, REGEXP_REPLACE достаточно гибок.
Используя следующую строку в качестве теста:
chr(9) || 'Q qwer' || chr(9) || chr(10) ||
chr(13) || 'qwerqwer qwerty' || chr(9) ||
chr(10) || chr(13)
[[:space:]] удалит все пробелы, а ([[:cntrl:]])|(^\t) regexp удалит непечатные символы и вкладки.
select
tester,
regexp_replace(tester, '(^[[:space:]]+)|([[:space:]]+$)',null)
regexp_tester_1,
regexp_replace(tester, '(^[[:cntrl:]^\t]+)|([[:cntrl:]^\t]+$)',null)
regexp_tester_2
from
(
select
chr(9) || 'Q qwer' || chr(9) || chr(10) ||
chr(13) || 'qwerqwer qwerty' || chr(9) ||
chr(10) || chr(13) tester
from
dual
)
Возвращение:
- REGEXP_TESTER_1: "
Qqwerqwerqwerqwerty" - REGEXP_TESTER_2: "
Q qwerqwerqwer qwerty"
Надеюсь, что это полезно.
Вы можете использовать как LTRIM, так и RTRIM.
select rtrim(ltrim('abcdab','ab'),'ab') from dual;
Если вы хотите обрезать CHR(13) только тогда, когда он идет с CHR(10), это усложняется. Во-первых, переводится объединенная строка в один символ. Затем LTRIM / RTRIM этот символ, а затем заменить один символ обратно в объединенную строку.
select replace(rtrim(ltrim(replace('abccccabcccaab','ab','#'),'#'),'#'),'#','ab') from dual;
TRANSLATE (column_name, 'd'||CHR(10)||CHR(13), 'd')
'D' является фиктивным символом, потому что перевод не работает, если 3-й параметр является нулевым.
Для какой версии Oracle? 10g+ поддерживает регулярные выражения - см. Его ветку на дискуссионном форуме OTN о том, как использовать REGEXP_REPLACE для преобразования непечатных символов в '',
Я знаю, что это не точный ответ на этот вопрос, но я работал в нескольких сценариях, где вам нужно преобразовать текстовые данные, следуя этим правилам:
- Без пробелов и символов Ctrl в начале строки
- Нет пробелов или символов Ctrl в конце строки
- Несколько вхождений пробелов или символов ctrl будут заменены на один пробел
Код ниже следуйте правилам, описанным выше:
WITH test_view AS (
SELECT CHR(9) || 'Q qwer' || CHR(9) || CHR(10) ||
CHR(13) || ' qwerqwer qwerty ' || CHR(9) ||
CHR(10) || CHR(13) str
FROM DUAL
) SELECT
str original
,TRIM(REGEXP_REPLACE(str, '([[:space:]][[:space:]]+)|([[:cntrl:]]+)', ' ')) fixed
FROM test_view;
ORIGINAL FIXED
---------------------- ----------------------
Q qwer Q qwer qwerqwer qwerty
qwerqwer qwerty
1 row selected.
Код Fowloing удаляет новую строку с обеих сторон строки:
select ltrim(rtrim('asbda'||CHR(10)||CHR(13) ,''||CHR(10)||CHR(13)),''||CHR(10)||CHR(13)) from dual
но в большинстве случаев этого достаточно:
select rtrim('asbda'||CHR(10)||CHR(13) ,''||CHR(10)||CHR(13))) from dual
Если кто-то вообще хочет преобразовать данные в 1 переменную, которая находится в 2 или 3 разных строках, как показано ниже
'Data1
Data2'
И вы хотите отображать данные как "Data1 Data2", затем используйте ниже
select TRANSLATE ('Data1
Data2', ''||CHR(10), ' ') from dual;
Мне потребовалось несколько часов, чтобы получить нужный результат. Благодаря мне я сэкономил вам 1-2 часа:)
Вместо того, чтобы использовать regexp_replace многократное использование (\s) как указано ниже;
SELECT regexp_replace('TEXT','(\s)','')
FROM dual;
CREATE OR REPLACE FUNCTION TRIM_WHITESPACE(pSTRIN IN NVARCHAR2) RETURN NVARCHAR2
IS
v_length NUMBER := COALESCE(LENGTH(pSTRIN), 0);
v_start_index NUMBER := 1;
v_end_index NUMBER := v_length;
BEGIN
IF pSTRIN IS NULL THEN
RETURN pSTRIN;
END IF;
-- Remove leading whitespace characters
FOR i IN 1..v_length LOOP
IF ASCII(SUBSTR(pSTRIN, i, 1)) NOT IN (9, 10, 13, 32) THEN
v_start_index := i;
EXIT;
END IF;
END LOOP;
-- Remove trailing whitespace characters
FOR i IN REVERSE v_start_index..v_length LOOP
IF ASCII(SUBSTR(pSTRIN, i, 1)) NOT IN (9, 10, 13, 32) THEN
v_end_index := i;
EXIT;
END IF;
END LOOP;
-- Return the trimmed string
RETURN SUBSTR(pSTRIN, v_start_index, v_end_index - v_start_index + 1);
END;
Попробуйте код ниже. Это будет работать, если вы введете несколько строк в одном столбце.
create table products (prod_id number , prod_desc varchar2(50));
insert into products values(1,'test first
test second
test third');
select replace(replace(prod_desc,chr(10),' '),chr(13),' ') from products where prod_id=2;
Выход: тест первый тест второй тест третий
Код ниже может быть использован для удаления новой строки и табличного пространства в текстовом столбце
Select replace(replace(TEXT,char(10),''),char(13),'')
В тех случаях, когда решение Oracle кажется чрезмерно запутанным, я создаю Java-класс со статическими методами, а затем устанавливаю его как пакет в Oracle. Это может быть не так эффективно, но в конечном итоге вы обнаружите другие случаи (например, преобразование даты в миллисекунды), в которых вам будет полезен запасной вариант Java.
UPDATE My_Table
SET Mycolumn1 =
TRIM (
TRANSLATE (Mycolumn1,
CHR (10) || CHR (11) || CHR (13),
' '))
WHERE ( INSTR (Mucolumn1, CHR (13)) > 0
OR INSTR (Mucolumn1, CHR (10)) > 0
OR INSTR (Mucolumn1, CHR (11)) > 0);