Выпуск 6

Удаление специальных символов

Уважаемые подписчики! Этот выпуск посвящен одному из вопросов, на которые ответил на днях Том Кайт на сайте asktom.oracle.com, а также общему синтаксису пакетов.

Дядю Тома спросили про удаление специальных символов

Как удалить из строки все специальные символы? Я не знаю встроенной функции Oracle, которая позволила бы это сделать. Есть ли пакеты, которые могут в этом помочь? Я хочу удалить все символы, кроме A-Z, a-z, 0-9.

Спасибо за помощь,
Лиза

и он ответил

Такой встроенной функции нет, но можно создать свою. Например, так:

ops$tkyte@8i> create or replace package utils
  2  as
  3      function strip_bad( p_string in varchar2 )
  4      return varchar2;
  5  end;
  6  /

Package created.

ops$tkyte@8i> 
ops$tkyte@8i> 
ops$tkyte@8i> create or replace package body utils
  2  as
  3  
  4  g_bad_chars   varchar2(256);
  5  g_a_bad_char  varchar2(256);
  6  
  7  function strip_bad( p_string in varchar2 ) return varchar2
  8  is
  9  begin
 10      return replace(
 11                 translate( p_string,
 12                            g_bad_chars,
 13                            g_a_bad_char ),
 14                 substr( g_a_bad_char, 1, 1 ),
 15                 '' );
 16  end;
 17  
 18  
 19  
 20  begin
 21      for i in 0..255 loop
 22          if ( i not between ascii('a') and ascii('z') AND
 23               i not between ascii('A') and ascii('Z') AND
 24               i not between ascii('0') and ascii('9') )
 25          then
 26              g_bad_chars := g_bad_chars || chr(i);
 27          end if;
 28      end loop;
 29      g_a_bad_char := rpad(
 30                        substr(g_bad_chars,1,1),
 31                        length(g_bad_chars),
 32                        substr(g_bad_chars,1,1));
 33  end;
 34  /

Package body created.

ops$tkyte@8i> select
  2  utils.strip_bad( 'How is this?' ) ,
  3  dump( utils.strip_bad( 'How is this?' )  )
  4  from dual;

UTILS.STRIP_BAD('HOWISTHIS?')
---------------------------------------------------------------------------
------
-------------------
DUMP(UTILS.STRIP_BAD('HOWISTHIS?'))
---------------------------------------------------------------------------
------
-------------------
Howisthis
Typ=1 Len=9: 72,111,119,105,115,116,104,105,115

Обсуждение

Очень хорошо, но... Комментарий Эрика, 17 мая 2002 года

Мне непонятно, почему весь этот код не помещен в функцию. Несколько странно.

Ответ дяди Тома

Просто мне нужна была строка символов, которая строится программно.

Я хочу программно строить эту строку символов ОДИН раз в сеансе. Поэтому я и использовал код начальной инициализации в теле пакета, который будет выполняться один раз, при первом использовании пакета в сеансе.

Можно ли объяснить этот код подробнее? 25 июня 2002 года

Если просто вызывается функция, выполняется ли этот цикл?
В какой последовательности все будет выполняться?
Что, если строка большая, varchar2(4000), и строк около 2 миллионов?
(У меня есть поле комментария с такими интересными символами. Я хочу либо удалить их, либо заменить пробелами, но при этом тип данных - varchar2(4000)!!)

Можно ли в этом случае сделать то же самое?

Как избавиться от символов типа '~!@#$%^&*()'

Есть ли обратная функция для ascii()?

Ответ дяди Тома

Этот цикл выполняется при первом использовании пакета в сеансе. Так что, да, при первом вызове функции пакета этот цикл выполняется. Ни один из последующих вызовов "циклиться" не будет.

Выполняться все будет так, что этот код автоматически и "чудом" вызывается машиной plsql ПЕРЕД первым использованием любого компонента пакета. Как конструктор в Java или C++. Это - "чудо"

Да, это можно проделать с 2 миллионами строк типа varchar2(4000). Надо только иметь терпение.

Ну, и в чем проблема с этими символами? Если вы хотите их сохранить, измените соответственно код.

chr - обратная функция для ascii

Оригинал обсуждения этого вопроса можно найти здесь. Некоторые несущественные детали и комментарии читателей не переведены.

Синтаксис спецификации и тела пакета

Раз уж мы затронули тему пакетов, немного синтаксиса - спецификация и тело пакета в Oracle 8.1.7 (в том числе, раздел инициализации)

Спецификация пакета

<спецификация пакета> ::=

[CREATE [OR REPLACE]] PACKAGE <имя пакета> [<авторизация>] <as или is>
[PRAGMA SERIALLY_REUSABLE;]
<объявление в спецификации пакета> {<объявление в спецификации пакета>}
END [<имя пакета>];

<as или is> ::=

AS | IS

<объявление в спецификации пакета> ::=

<объявление локального объекта в пакете>
| <спецификация>
| <прагма>

<объявление объекта в пакете> ::=

<объявление константы>
| <объявление переменной>
| <объявление записи>
| <объявление набора>
| <объявление объекта>
| <объявление типа>
| <объявление исключительной ситуации>
| <объявление курсора>

<спецификация> ::=

<спецификация курсора>
| <спецификация процедуры>
| <спецификация функции>
| <спецификация вызова внешней функции>

Тело пакета

<тело пакета> ::=

[CREATE [OR REPLACE]] PACKAGE BODY <имя пакета> <as или is>
[PRAGMA SERIALLY_REUSABLE;]
{<объявление объекта или прагма>}
{<спецификация>}
{<тело>}
[<раздел инициализации>]
END [<имя пакета>];

<объявление объекта или прагма> ::=

<объявление объекта в пакете>
| <прагма>

<тело> ::=

<тело курсора> | <объявление процедуры>
| <объявление функции>

<раздел инициализации> ::=

BEGIN <последовательность операторов>
[EXCEPTION <обработчик исключительной ситуации>
{<обработчик исключительной ситуации>}]

В следующем выпуске

Про то, как дядя Том получает такие красивые приглашения в командной строке SQL*Plus. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.