Oracle удалить перенос строки
Есть ли способ удалить возврат каретки из строки, загружаемой из csv.
Ячейка в моем образце csv выглядит следующим образом:
Когда я загружаю этот csv, строки этой ячейки делают новые записи. Я не хочу, чтобы это случилось. Я использую UTL_FILE для загрузки данных, и я разработал для этого хранимую процедуру.
DDL функции Plsql_Delimited_String.Getstring:
Я хочу знать, есть ли способ исключить возврат каретки из входящих данных и сделать значение (как упомянуто выше) в виде одной строки.
Согласно моему анализу, когда строка читается во время вызова функции UTL_FILE.Get_Line (Infile, Linebuf). Эта функция почему-то считывает только первую строку строки. Другими словами, читается только эта строка «Администратор ,, admin (admin): Mon Jan 25 09:48:22 GMT 2016, а остальная часть строки просто игнорируется и рассматривается как новая строка.
Я хочу знать, как мне написать такой цикл, который будет продолжать читать строку, пока не найдет всю строку.
3 ответа
Если я правильно понимаю проблему, в верхней части цикла, в котором вы вызываете UTL_FILE.Get_Line (Infile,Linebuf); , вы пытаетесь прочитать введенную пользователем текстовую строку (логически одно поле для перехода в один столбец), но эта текстовая строка содержит символы возврата каретки который UTL_FILE.Get_Line не обрабатывает и который вам нужно удалить.
Можете ли вы заставить исходную систему вырезать их, прежде чем вы получите файл, или вы можете предварительно обработать файл перед его чтением? Поскольку вы использовали слово «ячейка», я предполагаю, что данные поступают из электронной таблицы. Может ли ваш источник данных или вы в первую очередь ищете / заменяете его? Это было бы предпочтительнее.
BIG CAVEAT текст не может содержать двойные кавычки: - / Я настоятельно рекомендую очистить текст по источнику или предварительно обработать его перед чтением.
РЕДАКТИРОВАТЬ: Еще одна мысль; может ли источник предоставить вам длину данных в ячейке (поле)? Если это было вам доступно, есть третий аргумент для GET_LINE (), который представляет собой длину данных для чтения, которые заменяют GET_LINE (), останавливающуюся на символе конца строки. Прочитать tmplinebuf, использовать REPLACE (), чтобы удалить CHR (10), добавить tmplinebuf к linebuf?
Спасибо всем, кто нашел время и помог мне. Ну вот и решение. Это немного медленно, но работает.
Если вы ищете разделитель цитируемого поля, вы должны использовать
Как подстрока для функции instr.
Вы можете использовать replace, чтобы удалить символ возврата каретки, как этот, и вы можете удалить двойные кавычки в функциях GetString с помощью Trim
Вы также можете использовать CHR следующим образом: replace (str, chr (10), ''). или если у вас есть перевод строки & amp; возврат каретки, используйте chr (10) || chr (13) или chr (13) || chr (10).
Вы можете проверить, какой символ используется для торможения линии, используя шестнадцатеричный редактор или эхо-символ, используя
Вы можете удалить все символы разрыва строки, используя translate:
РЕДАКТИРОВАТЬ: Я понял вашу проблему после ответа Gary_W
Чтобы убедиться, что вы переместили все поле в строку, вы должны проверить последний прочитанный символ, если это двойная кавычка, то это конец строки, если не читать следующую строку и не добавить в буфер, поэтому попробуйте прочитать строку следующим образом:
Есть база данных, в ней есть некая таблица, в ней есть некое поле, в котором содержится текст. Как в этом тексте удалить все переносы строк, что бы текст стал одной единой строкой, средствами SQL? Не разумею. Гуглеж не помог. От документации уже голова едет. Желательно что бы решение не было привязано к конкретной СУБД, а было на чистом SQL. Но вообще дело происходит в Access.
Переносимое в голову не приходит, но должна быть функция применяющая регулярные выражения.
В postgresql: regexp_replace(string text, pattern text, replacement text [, flags text])
То есть: update table set column=regexp_replace(column,«\n»,"") where . ;
disarmer ★★★ ( 01.12.15 12:21:00 )
Последнее исправление: disarmer 01.12.15 12:22:44 (всего исправлений: 1)
В данном случае не обязательно регулярные выражения, простой замены достаточно.
Вот что первое выдал гугл по запросу mysql search and replace :
replace(string text, from text, to text) text Replace all occurrences in string of substring from with substring to replace('abcdefabcdef', 'cd', 'XX') abXXefabXXef
Это замечательно, но как мне указать замену именно переноса строки допустим на пробел?
Перенос строки это \n или \r\n (в каких-то экзотических случаях может встречаться \r без \n, хотя кажется такого уже не бывает).
Следовательно надо найти '\r\n' и заменить их на ' ', а потом найти '\n' и заменить на ' '
Последний перенос эта конструкция, вроде как не удаляет. На awk можно так:
Да, вы правы, не удаляет, удаляет только до последнего переноса. PS: А на sed можно как-то это замутить? Просто использую в основном именно его.
на гнутом sed можно так
sed довольно убог.
Что-то не сработало:
Главное, что ошибок никаких не выдает, а перенос остается, не удаляется:
А awk помог. Как же на sed сделать?
Походу он gsed вообще не соображает, есть ли перевод строки в конце файла или нет, так как одно и тоже регулярное выражение, выполняющее замену в последней строке, действует только на ту строку, в которой содержится текст, то есть не действует на пустую строку:
sed решает, что вторая строка является последней строкой в обоих случаях, даже если их три.
с чего ты взял что это подходящий инструмент, стандарт говорит:
Whenever the pattern space is written to standard output or a named file, sed shall immediately follow it with a .
Есть регулярное выражение, удаляющее последний символ в последней строке, но не символ перевода строки:
- не работает. Так все таки можно или нет удалить с помощью sed символ перевода строки \x0A в конце файла?
если двойные кавычки заменить на одинарные?
Да так, ради спортивного интереса) Я считаю, вы правы. PS: А drBatty есть на форуме? Может он скажет еще веское слово?
cmd.exe не понимает:
cmd.exe не понимает: sed.exe не понимает.
А что означает команда T в этой фразе?
А на sed можно как-то это замутить?
Sorry can't be done using sed
В своё время я тоже столкнулся с неудобствами, поэтому перешёл на perl.
Оказывается у меня была версия 4.0.7!
Символьная функция получает в качестве параметра одно или несколько символьных значений и возвращает символьное и числовое значение. Если символьная функция возвращает символьное значение, оно всегда имеет тип VARCHAR2 (переменная длина) — кроме функций UPPER и LOWER . Эти функции преобразуют заданную строку к верхнему или нижнему регистру соответственно и возвращают значение фиксированной длины типа CHAR , если переданные в аргументах строки имели тип CHAR .
Краткая сводка строковых функций
Как упоминалось ранее, PL/SQL предоставляет в распоряжение программиста широкий набор мощных, высокоуровневых строковых функций для получения информации о строках и модификации их содержимого. Следующий список дает представление об их возможностях и синтаксисе. За полной информацией о конкретных функциях обращайтесь к справочнику Oracle SQL Reference.
- ASCII(символ ) Возвращает числовой код (NUMBER) представления заданного символа в наборе символов базы данных.
- ASCIISTR(строка1) Получает строку в любом наборе символов и преобразует ее в строку ASCII-символов. Все символы, отсутствующие в кодировке ASCII, представляются в форме \XXXX, где XXXX — код символа в Юникоде.
- CHR(код)
Возвращает символ типа VARCHAR2 (длина 1), соответствующий заданному коду. Функция является обратной по отношению к функции ASCII. У нее имеется разновидность, удобная при работе с данными в национальных наборах символов:
Возвращает символ типа NVARCHAR2 из национального набора символов.
- COMPOSE(строка1)
Получает строку символов в формате Юникода и возвращает ее в нормализованном виде. Например, ненормализованное представление 'a\0303' определяет символ ' a ' с тильдой cверху (то есть a). Вызов COMPOSE('a\0303') возвращает значение ' \00E3' — шестнадцатеричный код символа a в Юникоде.
В Oracle9i Release 1 функция COMPOSE могла вызываться только из SQL-команд; в программах PL/SQL она использоваться не могла. Начиная с Oracle9i Release2, функция COMPOSE также может использоваться в выражениях PL/SQL.
- CONCAT(строка1, строка2)
Присоединяет строку2 в конец строки1. Аналогичного результата можно добиться при помощи выражения строка1 || строка2. Оператор || намного удобнее, поэтому функция CONCAT используется относительно редко. - CONVERT(строка1, набор_символов)
Преобразует строку из набора символов базы данных в заданный набор символов. При вызове также можно задать исходный набор символов:
CONVERT(строка1, конечный_набор, исходный_набор)
- DECOMPOSE(строка1)
Получает строку в Юникоде и возвращает строку, в которой все составные символы разложены на элементы. Функция является обратной по отношению к COMPOSE . Например, вызов DECOMPOSE ('a') возвращает строку ' a ~' (см. описание COMPOSE ).
Существует две разновидности этой функции:
- DECOMPOSE(строка1 CANONICAL)
Выполняет каноническую декомпозицию; полученный результат может быть восстановлен вызовом COMPOSE . Используется по умолчанию. - DECOMPOSE(строка1)
Декомпозиция выполняется в так называемом режиме совместимости. Восстановление вызовом COMPOSE может оказаться невозможным.
Функция DECOMPOSE , как и COMPOSE , не может напрямую вызываться в выражениях PL/SQL в Oracle9i Release 1; ее необходимо вызывать из инструкций SQL. Начиная с Oracle9i Release 2, это ограничение было снято.
Существует несколько разновидностей этой функции:
- INSTR(строка1, строка2, начальная_позиция)
Поиск строки2 в строке1 начинается с позиции, заданной последним параметром. По умолчанию поиск начинается с позиции 1, так что вызов INSTR(string1, string2, 1 ) эквивалентен вызову INSTR(string1, string2) . - INSTR(строка1, строка2, отрицательная_начальная_позиция)
Смещение начальной позиции задается не от начала, а от конца строки1. - INSTR(строка1, строка2, начальная_позиция, n )
Находит n-е вхождение строки2, начиная с заданной начальной позиции. - INSTR(строка1, строка2, отрицательная_начальная_позиция, n)
Находит n-е вхождение строки2, начиная с заданной начальной позиции от конца строки1.
Функция INSTR рассматривает строку как последовательность символов. Ее разновидности INSTRB, INSTR2 и INSTR4 рассматривают строку как последовательность байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность INSTRC рассматривает строку как последовательность полных символов Юникода. Например, строка 'a\0303' , которая представляет собой разложенный эквивалент '\00E3', или a , рассматривается как один символ. Напротив, функция INSTR рассматривает 'a\0303 ' как последовательность из двух символов.
- LEAST(строка1, строка2, . )
Получает одну или несколько строк и возвращает строку, которая оказалась бы первой (то есть наименьшей) при сортировке входных строк по возрастанию. Также см. описание функции GREATEST , обратной по отношению к LEAST . - LENGTH(строка1)
Возвращает количество символов в строке. Разновидности LENGTHB, LENGTH2 и LENGTH4 возвращают количество байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность LENGTHC возвращает количество полных символов Юникода, нормализованных по мере возможности (то есть с преобразованием 'a\0303 ' в '\00E3' ).
Функция LENGTH обычно не возвращает нуль. Вспомните, что Oracle рассматривает пустую строку ('') как NULL , поэтому вызов LENGTH ('') фактически эквивалентен попытке получить длину NULL ; ее результат тоже будет равен NULL . Единственное исключение встречается при применении LENGTH к типу CLOB . Тип CLOB может содержать 0 байт и при этом быть отличным от NULL . В этом единственном случае LENGTH возвращает 0.
- LOWER(строка1)
Преобразует все буквы заданной строки в нижний регистр. Функция является обратной по отношению к UPPER . Тип возвращаемого значения соответствует типу входных данных ( CHAR ,VARCHAR2, CLOB ). Также см. NLS_LOWER . - LPAD(строка1, итоговая_длина)
Возвращает значение строки1, дополненное слева пробелами до итоговой_длины . У функции существует следующая разновидность: - LPAD(строка1, итоговая_длина, заполнитель)
Присоединяет достаточное количество полных или частичных вхождений заполнителя, чтобы общая длина строки достигла заданной итоговой_длины . Например, вызов LPAD ( 'Merry Christmas!', 25, 'Ho! ') вернет результат ' Ho! Ho! HMerry Christmas!'.
- ?LTRIM(строка1)
Удаляет пробелы с левого края строки1. Также см. описания функций TRIM (стандарт ISO) и RTRIM . У функции существует следующая разновидность: - LTRIM(строка1, удаляемый_набор)
Удаляет любые символы, входящие в строку удаляемый_набор , от левого края строки1. - NCHR(код)
Возвращает символ типа NVARCHAR2 (длина 1) , соответствующий заданному коду. Функция CHR с условием USING NCHAR_CS реализует ту же функциональность, что и NCHR . - NLS_INITCAP (строка1)
Возвращает версию строки1, которая должна относиться к типу NVARCHAR2 или NCHAR , в которой первая буква каждого слова переводится в верхний регистр, а остальные буквы — в нижний. Функция возвращает значение типа VARCHAR2 . «Словом» считается последовательность символов, отделенная от остальных символов пробелом или символом, не являющимся буквенно-цифровым.
Вы можете задать порядок сортировки, влияющий на определение «первой буквы»:
- NLS_INITCAP(строка1, 'NLS_SORT=правило_сортировки')
В этой форме синтаксиса правило_сортировки представляет собой одно из допустимых названий правил сортировки, перечисленных в руководстве Oracle Database Globalization Support Guide, Appendix A, раздел «Linguistic Sorts».
Следующий пример показывает, чем функция INITCAP отличается от NLS_INITCAP :
В нидерландском языке последовательность символов « ? » рассматривается как один символ. Функция NLS_INITCAP распознает это обстоятельство при задании правила NLS_SORT и правильно преобразует символы слова « ?zer » («железо» по-нидерландски).
- NLS_LOWER(строка1) и NLS_LOWER(строка1, 'NLS_SORT=правило_сортировки ') Возвращает строку1, преобразованную в нижний регистр по правилам заданного языка. О том, как NLS_SORT может повлиять на результат преобразования, рассказано в описании функции NLS_INITCAP .
- NLS_UPPER(строка1) и NLS_UPPER(строка1, 'NLS_SORT=правило_сортировки') Возвращает строку1, преобразованную в верхний регистр по правилам заданного языка. О том, как NLS_SORT может повлиять на результат преобразования, рассказано в описании функции NLS_INITCAP .
- NLSSORT(строка1) и NLSSORT(строка1, 'NLS_SORT=правило_сортировки ') Возвращает строку байтов, которая может использоваться для сортировки строкового значения по правилам заданного языка. Строка возвращается в формате RAW . Например, сравнение двух строк по правилам французского языка выполняется так: IF NLSSORT(x, 'NLS_SORT=XFRENCH') > NLSSORT(y, 'NLS_SORT=XFRENCH') THEN. Если второй параметр не указан, функция использует порядок сортировки по умолчанию, назначенный для сеанса. Полный список правил приведен в руководстве Oracle Database Globalization Support Guide, Appendix A, раздел «Linguistic Sorts».
- REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR За описаниями этих функций, предназначенных для работы с регулярными выражениями, можно изучить эту статью.
- REPLACE(строка1, искомая_строка, замена) Возвращает строку, полученную в результате замены всех вхождений искомой_строки в строке1 строкой замена. Функция REPLACE может использоваться для замены всех вхождений определенной подстроки в одной инструкции.
- REPLACE(строка1, искомая_строка)
Возвращает строку, полученную в результате удаления всех вхождений искомой_строки из строки1. - RPAD(строка1, итоговая_длина)
Возвращает значение строки1, дополненное справа пробелами до итоговой_длины . У функции существует следующая разновидность: - RPAD(строка1, итоговая_длина, заполнитель)
Присоединяет достаточное количество полных или частичных вхождений заполнителя, чтобы общая длина строки достигла заданной итоговой_длины . Вызов RPAD('Merry Christmas!', 25, 'Ho! ') вернет результат 'Merry Christmas! Ho! Ho!'.
Функция RPAD дополняет строку справа, а парная ей функция LPAD — слева.
- RTRIM(строка1)
Удаляет пробелы с правого края строки1. Также см. описания функций TRIM (стандарт ISO) и LTRIM . У функции существует следующая разновидность: - RTRIM(строка1, удаляемый_набор)
Удаляет любые символы, входящие в строку удаляемый_набор , с правого края строки1. - SOUNDEX(строка1)
Возвращает строку с «фонетическим представлением» аргумента.
Пример:
При использовании функции SOUNDEX следует помнить несколько правил:
- Значение SOUNDEX всегда начинается с первой буквы входной строки.
- Возвращаемое значение генерируется только по первым пяти согласным в строке.
- Для вычисления цифровой части SOUNDEX используются только согласные. Все гласные в строке, кроме начальных, игнорируются.
- Функция SOUNDEX игнорирует регистр символов; для букв верхнего и нижнего регистра генерируются одинаковые значения SOUNDEX .
Функция SOUNDEX полезна для запросов, при которых точное написание значения в базе данных неизвестно или не может быть легко определенно.
Алгоритм SOUNDEX ориентирован на английский язык; в других языках он может работать плохо (или не работать вообще).
- SUBSTR(строка1, начальная_позиция, длина)
Возвращает подстроку из строки1, которая начинается с начальной_позиции и имеет заданную длину. Если количество символов до конца строки1 окажется меньше длины, возвращаются все символы от начальной позиции до конца строки. У функции существуют следующие разновидности: - SUBSTR(строка1, начальная_позиция)
Возвращает все символы от начальной_позиции до конца строки1. - SUBSTR(строка1, отрицательная_начальная_позиция, длина)
Начальная позиция подстроки отсчитывается от конца строки1. - SUBSTR(строка1, отрицательная_начальная_позиция)
Возвращает последние ABS( отрицательная_начальная_позиция ) строки.
Функция SUBSTR рассматривает строку как последовательность символов. Ее разновидности SUBSTRB, SUBSTR2 и SUBSTR4 рассматривают строку как последовательность байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность SUBSTRC рассматривает строку как последовательность полных символов Юникода. Например, строка 'a\0303' , которая представляет собой разложенный эквивалент '\00E3' , или a , рассматривается как один символ. Напротив, функция SUBSTR рассматривает 'a\0303' как последовательность из двух символов.
- TO_CHAR(национальные_символьные_данные)
Преобразует данные из национального набора символов в эквивалентное представление в наборе символов базы данных. Также см. TO_NCHAR .
Функция TO_CHAR также может использоваться для преобразования даты/ времени и чисел в удобочитаемую форму.
- TO_MULTI_BYTE(строка1)
Преобразует однобайтовые символы в их многобайтовые эквиваленты. В некоторых многобайтовых кодировках, и прежде всего UTF-8, может существовать несколько вариантов представления одного символа. Скажем, в UTF-8 представление буквы 'G' может содержать от 1 до 4 байт. Для перехода от однобайтового представления к многобайтовому используется функция TO_MULTI_BYTE . Данная функция является обратной по отношению к TO_SINGLE_BYTE . - TO_NCHAR(символы_в_наборе_базы_данных)
Преобразует данные из набора символов базы данных в эквивалентное представление в национальном наборе символов. Также см. TO_CHAR и TRANSLATE. USING.
Функция TO_NCHAR также может использоваться для преобразования даты/времени и чисел в удобочитаемую форму.
- TO_SINGLE_BYTE(строка1)
Преобразует многобайтовые символы в их однобайтовые эквиваленты. Функция является обратной по отношению к TO_MULTI_BYTE . - TRANSLATE(строка1, искомый_набор, набор_замены)
Заменяет в строке1 каждое вхождение символа из искомого_набора соответствующим символом набора_замены . Пример:
Если искомый_набор содержит больше символов, чем набор_замены , «лишние» символы, не имеющие соответствия в наборе_замены , не включаются в результат. Пример:
Буква « d » удалена, потому что она присутствует в искомом_наборе , но не имеет эквивалента в наборе_замены . Функция TRANSLATE заменяет отдельные символы, а функция REPLACE — целые строки.
- TRANSLATE(текст USING CHAR_CS) и TRANSLATE(текст USING NCHAR_CS)
Преобразует символьные данные в набор символов базы данных ( CHAR_CS ) или в национальный набор символов ( NCHAR_CS ). Выходным типом данных будет VARCHAR2 или NVARCHAR2 в зависимости от того, выполняется ли преобразование к набору символов базы данных или национальному набору символов соответственно.
Функция TRANSLATE. USING входит в число функций SQL по стандарту ISO. Начиная с Oracle9i Release 1, можно просто присвоить значение VARCHAR2 переменной типа NVARCHAR2 , и наоборот — система неявно выполнит нужное преобразование. Если же вы хотите выполнить преобразование явно, используйте функции TO_CHAR и TO_NCHAR для преобразования текста в набор символов базы данных и национальный набор символов соответственно. Oracle рекомендует пользоваться указанными функциями вместо TRANSLATE. USING , потому что они поддерживают более широкий набор входных типов данных.
- TRIM(FROM строка1)
Возвращает строку, полученную в результате удаления из строки1 всех начальных и конечных пробелов. У функции существуют следующие разновидности: - TRIM(LEADING FROM . )
Удаление только начальных пробелов. - TRIM(TRAILING FROM . )
Удаление только конечных пробелов. - TRIM(BOTH FROM . )
Удаление как начальных, так и конечных пробелов (используется по умолчанию). - TRIM (. удаляемый_символ FROM строка1)
Удаление вхождений одного удаляемого_символа на выбор программиста.
Функция TRIM была включена в Oracle8i для обеспечения более полной совместимости со стандартом ISO SQL. Она сочетает в себе функциональность LTRIM и RTRIM , но отличается от них тем, что TRIM позволяет задать только один удаляемый символ, тогда как при использовании LTRIM и RTRIM можно задать набор удаляемых символов.
- UNISTR(строка1)
Возвращает строку1, преобразованную в Юникод; таким образом, функция является обратной по отношению к ASCIISTR . Для представления непечатаемых символов во входной строке можно использовать запись \XXXX, где XXXX — кодовый индекс символа в Юникоде. Пример:
Функция предоставляет удобный доступ ко всему набору символов Юникода, в том числе и к тем, которые не могут вводиться непосредственно с клавиатуры.
Встроенная функция PL/SQL CHR особенно удобна в тех случаях, когда в программный код необходимо включить ссылку на непечатаемый символ (спецсимвол). Допустим, вы строите отчет, в котором выводятся адреса компаний. Помимо строк с названиями города, страны и индекса адрес может содержать до четырех дополнительных строк, и значение каждой из них должно выводиться с новой строки. Все строки адреса можно объединить в одно длинное текстовое значение и использовать функцию CHR для вставки разрывов строк в нужных местах. В стандартной кодировке ASCII символ новой строки имеет код 10, поэтому программа может выглядеть так:
Предположим, в таблицу была вставлена следующая строка:
Вывод будет выглядеть примерно так:
Символ с кодом 10 обозначает разрыв строки в системах Linux и Unix. В Windows для этой цели используется комбинация символов CHR(12)||CHR(10). Возможно, в других средах вам придется использовать какие-то другие символы.
Не хотите, чтобы в выходных данных присутствовали пустые строки? Нет проблем. Задача легко решается умным использованием функции NVL2 :
Теперь запрос возвращает один отформатированный столбец на компанию. Функция NVL2 возвращает третий аргумент, если первый аргумент равен NULL , или второй аргумент во всех остальных случаях. В данном примере, если значение address1 равно NULL , возвращается пустая строка (''); то же самое происходит с остальными столбцами адресов.
В результате пустая строка исчезает из адреса:
Функция ASCII фактически является обратной по отношению к CHR: она возвращает десятичное представление заданного символа в наборе символов базы данных. Например, следующий фрагмент выводит десятичный код буквы «J»:
Как выясняется, буква «J» (по крайней мере в кодировке UTF-8) представлена кодом 74.
Интересные примеры использования функции CHR приводятся далее, в разделе «Традиционный поиск и замена».
Читайте также: