Oracle конвертирует строку в число

Обновлено: 21.11.2024

Получите полный доступ к Oracle SQL: Essential Reference и более чем 60 000 других наименований с бесплатной 10-дневной пробной версией O'Reilly.

Есть также прямые онлайн-мероприятия, интерактивный контент, материалы для подготовки к сертификации и многое другое.

Преобразует числовое значение в символьное представление этого значения.

Параметры

Указывает числовое значение для преобразования.

Задает спецификацию числового формата, состоящую из элементов, показанных в таблице 5-5.

Определяет строку символов, состоящую из одного или нескольких следующих элементов, что позволяет указать различные характеристики NLS для результата.

NLS_NUMERIC_CHARACTERS='' dg ''

Позволяет указать символы, используемые для десятичных запятых и разделителей групп. Символ d становится десятичной точкой, а символ g становится разделителем групп. Чтобы этот параметр вступил в силу, вы должны использовать D и G в спецификации формата, чтобы отметить расположение десятичной точки и разделителя групп соответственно.

NLS_CURRENCY='' текст ''

Позволяет указать символ валюты (длиной до 10 символов) для использования вместо любых L символов, содержащихся в спецификации формата.

NLS_ISO_CURRENCY='' территория ''

Позволяет указать территорию NLS, символ валюты которой вы хотите использовать вместо любых символов C, содержащихся в спецификации формата.

Параметры nlsparams представляют собой строки, встроенные в строку. Кавычки, которые вы видите вокруг значений, представляют собой двойные одинарные кавычки, а не двойные кавычки.

Пример

Получите Oracle SQL: основной справочник прямо сейчас с онлайн-обучением O’Reilly.

Члены O’Reilly проходят онлайн-обучение в режиме реального времени, а также получают книги, видео и цифровой контент от более чем 200 издателей.

Функция Oracle TO_NUMBER — одна из наиболее распространенных функций обработки чисел в Oracle. В этой статье и видео я объясню, как использовать функцию TO_NUMBER.

Из этой статьи вы узнаете:

  • Что делает функция TO_NUMBER
  • Синтаксис и параметры функции TO_NUMBER
  • Несколько примеров запросов с функцией TO_NUMBER
  • Ответы на некоторые часто задаваемые вопросы об этой функции

Что такое функция Oracle TO_NUMBER?

Функция Oracle TO_NUMBER используется для преобразования текстового значения в числовое значение.

Она работает аналогично функциям TO_DATE и TO_CHAR, но преобразует значения в числа.

Функция принимает много разных типов данных:

  • BINARY_FLOAT
  • BINARY_DOUBLE
  • СИМВОЛ
  • VARCHAR2
  • ЧАР
  • NVARCHAR2

TO_NUMBER возвращает значение типа данных NUMBER.

Кроме того, число будет округлено до указанного количества цифр, что может привести к нежелательным последствиям.

Синтаксис

Синтаксис функции Oracle TO_NUMBER:

TO_NUMBER( input_value, [format_mask], [nls_parameter] )

Параметры

  • input_value (обязательный): это значение, которое необходимо преобразовать в число. Это может быть один из многих типов данных, упомянутых ранее, но чаще всего он предоставляется в виде строки.
  • format_mask (необязательно): это формат, в котором должно отображаться выходное значение. Это должен быть допустимый числовой формат, как описано ниже.
  • nls_parameter (необязательно): это значение используется для определения способа отображения выходного значения, например символов валюты и числовых символов.

Маска формата Oracle TO_NUMBER

TO_CHAR (преобразование чисел в строки символов)
< tr> < /tr>
Формат Пояснение
9< /td> Один числовой символ. Удаляет начальные и конечные нули. Включает начальный дефис (-) для отрицательных чисел.
0 Однозначный символ. Включает начальные и конечные нули.
FM Указывает, что формат должен подавлять все начальные и конечные пробелы
$ Включить начальный знак $
B Изменяет формат «0» и заменяет начальные 0 пустыми пробелы.
S Включить как положительные (+), так и отрицательные (-) знаки
PR Указать знак числа в угловых скобках (<>)
MI Указать знак минус
RN или rn Отображает число римскими цифрами (в верхнем или нижнем регистре)
D Указывает положение десятичной точки (.)
G Добавляет разделитель групп (часто запятую) в позицию
, Добавляет запятую в позицию
. Добавляет точку в позицию
C Добавляет символ валюты ISO
L Добавляет символ местной валюты
EEEE Отображает значение в экспоненциальном представлении
TM Отображает минимум текста в TM9 (по умолчанию) или TME (научное обозначение)
U Отображает в указанной позиции символ двойной валюты евро (или другого).
V Отображает значение, умноженное на 10 n , где n — количество девяток после буквы V. При необходимости значение можно округлить в большую сторону
X Возвращает шестнадцатеричное значение указанного количества цифр.

Если маска формата не указана, в качестве десятичного разряда используется символ точки (.), а другие символы не распознаются.

Хотите увидеть подобные руководства для всех других функций Oracle? Посетите эту страницу, на которой перечислены все функции SQL вместе со ссылками на их руководства.

Объяснение NLS_PARAMETER

Этот параметр используется для добавления в функцию дополнительной информации для отображения значения. Она работает аналогично функции TO_CHAR, но имеет несколько отличий.

Добавленное здесь выражение будет представлять маркер группы тысяч, десятичный маркер и используемые символы валюты. Выражение для этого параметра может содержать один или несколько следующих параметров, разделенных запятыми:

  • NLS_CURRENCY символ
  • NLS_ISO_CURRENCY территория
  • NLS_NUMERIC_CHARACTERS dg

С этими утверждениями:

  • символ представляет символ местной валюты и должен быть меньше или равен 10 символам.
  • территория – это текстовое выражение, определяющее территорию, для которой используется символ валюты ISO.
  • dg представляет два символа, которые являются однобайтовыми символами и используются для десятичного маркера и маркера группы тысяч.

Примеры функции TO_NUMBER

Вот несколько примеров функции TO_NUMBER. Я считаю, что примеры — лучший способ узнать о коде, даже с объяснением выше.

Во всех этих примерах используется таблица DUAL, которая представляет собой таблицу в Oracle, позволяющую отображать данные в операторе SELECT без необходимости создавать или выбирать из таблицы в базе данных. Связано: таблица Oracle DUAL.

Пример 1. Формат по умолчанию

В этом примере простая строка преобразуется в числовое значение.

Пример 2. Заданный формат с десятичными знаками

В этом примере та же строка преобразуется в число в указанном формате.

Пример 3. Формат без десятичных знаков

В этом примере преобразуется число, в котором нет десятичных разрядов.

Пример 4. Формат с валютой

В этом примере знак доллара рассматривается как цифра денежной единицы и преобразуется в число.

Пример 5. Формат с маркерами тысяч групп

В этом примере обрабатываются запятые, используемые в качестве маркеров тысяч групп.

Пример 6. Использование параметра NLS

В этом примере берется сложная строка, определяется, что она имеет определенный формат, и указываются числовые символы, которые нужно использовать

TO_NUMBER часто задаваемых вопросов

Почему TO_NUMBER выдает ошибку "Неверный номер"?

Иногда вы можете получить следующую ошибку при попытке использовать функцию TO_NUMBER:

Это связано с тем, что значение в поле TO_NUMBER не является числом, а должно быть.

Это может происходить по нескольким причинам:

  • Тип данных столбца, который вы предоставляете, не является числом.
  • Тип данных столбца, который вы ему предоставляете, не является числом, и он содержит числа, хранящиеся в виде строк, но было обнаружено нечисловое значение.
  • Данные в столбце содержат пробелы
  • В предложении WHERE происходит преобразование, которое приводит к преобразованию данных в другой тип.

Итак, чтобы исправить ошибку с недопустимым номером to_number:

  • Если вы выполняете операцию INSERT INTO, вам необходимо выяснить, какой столбец и значение вызывают проблему. Попробуйте SELECT DISTINCT для столбца, чтобы увидеть, в чем проблема.
  • Если вы выполняете ОБНОВЛЕНИЕ, причиной может быть неверный тип данных в поле, из которого выполняется обновление.
  • Если вы выполняете SELECT, это также может быть связано с тем, что тип данных не соответствует ожидаемому. Вам нужно выяснить, какие данные вызывают проблему, чтобы понять, почему ваш SELECT TO_NUMBER не работает.

Можно ли использовать Oracle TO_NUMBER с датами?

Да и нет. Вы не можете указать значение даты в качестве параметра напрямую:

Но вы можете преобразовать его в строковое значение, а затем использовать его в качестве параметра, чтобы использовать Oracle TO_NUMBER с датой:

Можно ли сохранить ведущие нули с помощью Oracle TO_NUMBER?

Ну, в числовых типах Oracle нет начальных нулей. Нули в начале используются только для строковых типов (например, VARCHAR2).

Поэтому, если вы используете Oracle TO_NUMBER для начальных нулей, он удалит нули из исходного значения.

Возможные ошибки при использовании функции TO_NUMBER

  • ORA-01722: неверный номер. Это происходит, когда входное значение не представляет число. Это также может произойти, если строка, указанная в качестве маски формата, неверна.

Похожие функции

    – преобразует строковое значение в тип DATE. – преобразует числовое значение или значение даты в строковый тип. - используется для многих типов, включая даты

Вы можете найти полный список функций Oracle здесь.

7 мыслей на тему “Использование функции Oracle TO_NUMBER, советы и примеры”

Я пытаюсь найти число вроде 61 123456 в диапазоне. Это хранится как varchar в БД.
я сделал что-то вроде этого TO_NUMBER('61 123456′) BETWEEN
TO_NUMBER(REGEXP_REPLACE(TN_RANGE_STRT,'\s')) AND TO_NUMBER(REGEXP_REPLACE(TN_RANGE_END,'\s')) но это не так работает

Возможно, вам потребуется использовать один и тот же метод преобразования как для входного значения (61 123456), так и для столбцов базы данных.
Не могли бы вы сначала выбрать значения, а затем посмотреть, как они отображаются?
Например.
ВЫБЕРИТЕ tn_range_strt, tn_range_end, TO_NUMBER(REGEXP_REPLACE(TN_RANGE_STRT,’s’)), TO_NUMBER(REGEXP_REPLACE(TN_RANGE_END,’s’))
ИЗ вашей таблицы;

Таким образом, вы можете увидеть, как выглядят значения, и увидеть, показывают ли ваши функции то, что вам нужно.

Я пытаюсь использовать to_numer для преобразования varchr2 в «пять» или «четыре», как это работает?
to_number('четыре',9′) из двойного;

Здравствуйте, вам, вероятно, потребуется написать собственную функцию, чтобы перевести это. Я не думаю, что to_number может преобразовать записанное число в число.

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

Преобразует exp1 , значение типа данных CHAR, VARCHAR2, NCHAR или NVARCHAR2, содержащее число в формате, заданном необязательной моделью формата exp2 , в значение типа данных NUMBER.

Модель числового формата ( exp2 ) состоит из одного или нескольких элементов числового формата, перечисленных ниже:

Возвращает значение с указанным количеством цифр с начальным пробелом, если оно положительное, или с начальным минусом, если отрицательное.

Ведущие нули пусты, за исключением нулевого значения, которое возвращает ноль для целой части числа с фиксированной запятой.

Возвращает пробелы для целой части числа с фиксированной запятой, когда целая часть равна нулю (независимо от «0» в модели формата).

Возвращает в указанной позиции символ валюты ISO (текущее значение параметра NLS_ISO_CURRENCY в exp3 ).

Возвращает в указанной позиции десятичный символ, который является текущим значением параметра NLS_NUMERIC_CHARACTER в exp3 . По умолчанию используется точка (.).

Возвращает в указанной позиции разделитель групп (текущее значение параметра NLS_NUMERIC_CHARACTER в exp3 ). Несколько разделителей групп могут указываться в модели числового формата.

Возвращает в указанной позиции символ местной валюты (текущее значение параметра NLS_CURRENCY в exp3 ).

Возвращает отрицательное значение со знаком минус (-) в начале. Возвращает положительное значение со знаком плюс (+) в начале.

Ограничение: элемент формата S может появляться только в первой или последней позиции модели числового формата.

Возвращает отрицательное значение со знаком минус (-) в конце. Возвращает положительное значение со знаком плюс (+) в конце.

"Минимум текста". Возвращает (в десятичном формате) наименьшее возможное количество символов. Этот элемент нечувствителен к регистру.

По умолчанию используется TM9, который возвращает число в фиксированной записи, если длина вывода не превышает 64 символа. Если вывод превышает 64 символа, Oracle автоматически возвращает число в экспоненциальном представлении.

Возвращает в указанной позиции символ двойной валюты "евро" (или другой) (текущее значение параметра NLS_DUAL_CURRENCY в exp3 ).

Возвращает значение, умноженное на 10n (и, при необходимости, округлить его в большую сторону), где n – количество девяток после буквы "V".

Возвращает шестнадцатеричное значение указанного количества цифр. Если указанное число не является целым числом, Oracle округляет его до целого числа.

� Перед этим элементом может стоять только 0 (который возвращает ведущие нули) или FM. Любые другие элементы возвращают ошибку. Если вы не укажете ни 0, ни FM с X, то возврат всегда будет иметь 1 начальный пробел.

Если он использует числовые операции (например, +) и один из операндов является целым числом без знака, результат будет беззнаковым.Это можно переопределить, используя операторы приведения SIGNED и UNSIGNED для приведения операции к 64-битному целому числу со знаком или без знака соответственно.

Авторское право © Ispirer Systems Ltd, 1999-2015 гг. Ispirer и SQLWays являются товарными знаками Ispirer Systems Ltd. Все остальные названия продуктов могут быть товарными знаками соответствующих компаний. Все права защищены.

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

Для преобразования данных Oracle можно использовать следующие функции преобразования Oracle:

Функция преобразования Oracle to_char(), вероятно, является наиболее часто используемой функцией преобразования. Функция преобразования преобразует как числовые данные, так и данные даты в тип данных varchar2.

Самый простой способ использования to_char() для преобразования информации о дате Oracle заключается в следующем:

Это преобразует дату Oracle в строку символов в формате даты по умолчанию (числовой месяц, числовой день и двузначный год, разделенные косой чертой). Однако чаще всего to_char() используется для преобразования дат Oracle в тип varchar2 в определенном формате, как показано в этом примере,

который возвращает следующую строку:

Использование to_char() для преобразования числовых данных очень похоже на использование функции с датами. Одним из распространенных применений этой функции преобразования Oracle является простое преобразование данных из числового типа данных в тип varchar2. Например,

При преобразовании числовых данных Oracle to_char() также может принимать второй аргумент; модель формата для вывода функции. Например,

В главе 3 перечислены элементы, которые можно использовать в моделях формата для числовых данных и данных даты.

Функция преобразования Oracle to_date() используется для преобразования символьных данных в тип данных даты. Подобно to_char(), эта функция преобразования Oracle может вызываться с одним параметром, как

который возвращает значение типа date. to_date() также может вызываться со вторым параметром, который дает указание функции преобразовать указанную строку из указанного формата в стандартную дату. Например,

Действительные элементы модели формата обсуждаются в главе 3. Во всех приведенных здесь примерах используется формат даты Oracle по умолчанию.

Функция преобразования Oracle to_number() преобразует данные типа char или varchar2 в числовой тип. Эта функция преобразования Oracle может принимать один параметр. Например,

Функция преобразования Oracle также может принимать модель формата в качестве второго параметра, как и функции to_char() и to_date(). Например,

Действительные элементы модели формата обсуждаются в главе 3.

Это отрывок из книги "High Performance Oracle Database Automation", написанной Джонатаном Инграмом и Дональдом К. Берлесоном, редактором серии.

Читайте также: