Oracle убрать ведущие нули

Обновлено: 07.07.2024

Я работаю над запросом SQL, который считывает данные из базы данных SQLServer для получения файла извлечения. Одним из требований является удаление ведущих нулей из определенного поля, которое является простым полем VARCHAR(10) . Так, например, если поле содержит '00001A', оператор SELECT должен возвращать данные как '1A'.

Есть ли способ в SQL легко удалить ведущие нули таким образом? Я знаю, что есть функция RTRIM , но это, кажется, только для удаления пробелов.

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

У меня есть строка 0023525631 , но мне нужна строка 23525631 - без ведущих нулей. Как мне это сделать?

возвращает N0Z , то есть избавится от ведущих нулей и всего, что перед ними.

У меня была такая же потребность, и я использовал это:

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

Вы можете использовать это:

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

Или вы можете просто позвонить

Вот функция значения SQL scalar, которая удаляет начальные нули из строки:

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

Вы можете рассмотреть возможность использования команды STUFF.

Вот пример того, как это будет работать.

См. в fiddler различные сценарии, которые он будет охватывать

Возможный Дубликат : Удаление ведущих нулей из поля в операторе SQL В базе данных SQL Server 2008 у меня есть строковый столбец, который мне нужно выбрать с удаленным ведущим 0. E.g. 0023AFF должно быть возвращено как 23AFF . Возможно ли это в T-Sql и как?

Мне нужно int 32 в двоичном формате как 00100000 или int 127 в двоичном 0111 1111 . Вариант Integer.toBinaryString возвращает результаты только из 1. Если я построю for loop таким образом: for (int i= 32; i <= 127; i + +) < System.out.println (i); System.out.println (Integer.toBinaryString.

Чтобы удалить ведущий 0 из следующего заявления, безусловно, будет работать.

Просто замените GETDATE() полем даты вашей таблицы.

Чтобы удалить ведущий 0, вы можете умножить столбец чисел на 1 Например: Выберите (ColumnName * 1)

Я позаимствовал из приведенных выше идей. Это не быстро и не элегантно. но это точно.

вы можете попробовать это SELECT REPLACE(columnname,'0','') FROM table

Это сценарий Sql, который эмулирует функциональность команды TRIM в tsql до 2017 года,в основном такой же, как и другие рекомендации, но другие заменяют один необычный символ, который все еще может встречаться, "[Rtrim] " или "[Ltrim] " все еще могут встречаться в тексте, но замена hat уникальным текстом, например Guid, решит эту проблему.

я не проверял его на скорость

Этого должно хватить.

Похожие вопросы:

У меня есть строка 0023525631 , но мне нужна строка 23525631 - без ведущих нулей. Как мне это сделать?

Строчные функции конвертации типа данных разработаны для изменения типа данных столбца, выражения или литерала. Наиболее часто используемые функции конвертации это TO_CHAR, TO_NUMBER и TO_DATE. TO_CHAR преобразует числа и даты в символьные данные, когда TO_NUMBER и TO_DATE преобразует символьные данные соответственно в число и дату.

Функции конвертации

Oracle позволяет определять столбцы с типами данных ANSI, DB2 и SQL/DS. Эти типы преобразуется к типам данных Oracle. У каждого столбца определяется тип данных который ограничивает природу данных которые могут храниться в этом столбце. Столбец NUMBER не может хранить символьную информацию. Столбец DATE не может хранить случайные символы или числа. VARCHAR2 может хранить символьные эквиваленты чисел и дат.

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

Неявная конвертация типов

Значения, которые не соответствуют типам данных параметров функции неявно конвертируется перед выполнением если это возможно. Оба типа данных VARCHAR2 и CHAR используются как символьные типы данных. Символьные типы данных достаточно гибкие для хранения практически любой информации. Таким образом, ДАТА и ЧИСЛО можно легко преобразовать в их символьный эквивалент. Такая конвертация известна как преобразования число в строку и дата в строку. Рассмотрим следующие запросы

Query 1: select length(1234567890) from dual

Query 2: select length(SYSDATE) from dual

Оба запроса используют функцию LENGTH у которой входной параметр определён как строка. Число 1234567890 в запросе один неявно конвертируется в строку ‘1234567890’ перед вычисление функции LENGTH и результат функции будет 10. Запрос номер два вычисляет функцию SYSDATE предположим 7 апреля 2008 года. Результат преобразуется в строку ’07-APR-08’ и результат выполнения функции LENGTH будет число 9.

Обычно не принято допускать неявную конвертацию строк в числа, так как единственная ситуация, когда это возможно, это если строка представляет собой валидное число. Строка ‘11’ будет неявно преобразована в число 11, но строка ’11.123.345’ не будет, как показано в следующих примерах

Запросы 3 и 4 неявно преобразовали строки ‘11’ и ’11.123’ в числа 11 и 11.123 соответственно, перед вызовом функции MOD которая в свою очередь вернула результат 1 и 1.123. Запрос 5 вернул ошибку ‘ORA-1722: invalid number’, когда Oracle попытался неявно преобразовать строку в число, так как ‘11.123.456’ не является корректным числом. Запрос 6 также вернул ошибку так как символ доллара не может бять неявно преобразован в число.

Неявная конвертация строки в дату возможна, когда строка удовлетворяет следующим шаблонам: [D|DD] separator1 [MON|MONTH] separator2][R|RR|YY|YYYY], где D и DD это день MON первые три буквы месяца, MONTH – полное название месяца. R и RR YY и YYYY отображают одну, две и четыре цифры года соответственно. Параметром separator1 и separator2 может быть практически любой спец символ, включая сюда пробел, табуляцию, знаки пунктуации и т.д. Таблица 10-2 показывает неявную конвертацию строки в дату, включая вызов функций работы с датами и результаты. Эти результаты предполагают, что система использует американскую локаль.

TIP Несмотря на то что неявная конвертация типов доступна, лучше использовать явный вызов функции конвертации перед использование данным в других функциях. Конвертация строки в число и дату использует маску форматирования.

Явная конвертация типов данных

Функции, которые конвертирует значение из одного типа данных в другой известны как явное преобразование типов. Они возвращают значение, которое будет гарантировано нужного типа данных и являются надёжным методом конвертации данных.

Число и дату можно явно конвертировать в строку используя функцию TO_CHAR. Строку можно явно конвертировать в число используя функцию TO_NUMBER. Функция TO_DATE используется для конвертации строку в DATE. Маски форматирования Oracle позволяют гибко контролировать процесс конвертации строки в число или дату.

Использование функций TO_CHAR, TO_NUMBER и TO_DATE

Использование функций конвертации

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

Таблица 10-3 показывает синтаксис строчных функций конвертации данных

Необязательный параметр поддержки национального формата (nls_parameters) полезен для указания языка и форматирования, в котором названия дней, месяцев и разделители разрядов, целой и дробной части заранее предопределены. На рисунке 10-2 отображено представление NLS_SESSION_PARAMETERS которое содержит значения параментов NLS для текущей сессии. По умолчанию значение NLS_CURRENCY – знак доллара, но это можно изменить на уровне сессии. Например, для изменения символа валюты на строку ‘GBP’ можно выполнить запрос

ALTER SESSION SET NLS_CURRENCY=’GBP’

Рисунок 10-2 – Представление NLS_SESSION_PARAMETERS

Конвертация числа в строку используя функцию TO_CHAR

Функция TO_CHAR возвращает значение типа VARCHAR2. Когда входных параметром является число то доступны некоторые параметры форматирования. Синтаксис команды TO_CHAR(num, [format], [nls_parameter]). Параметр num обязательный и должен быть числом. Необязательный параметр format можно использовать для указания информации о форматировании, такой как длина, символ валюты, позиция разделителя дробной и целой части и разделитель разрядов (три разряда) и должен быть заключен в одинарные кавычки. Доступны различные опции форматирования и часть из них представлена в таблице 10-4. Рассмотрим два запроса

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

Конвертация даты в строку используя функцию TO_CHAR

Вы можете использовать преимущества модели масок форматирования при конвертации ДАТЫ в практически любой вариант отображения даты как символьного значения используя функцию TO_CHAR. Синтаксис функции TO_CHAR(date1, [format], [nls_parameter]).

Только параметр date1 обязательный; тогда он должен быть значением, которое может неявно преобразоваться в строку. Необязательный параметр format регистрозависимый и должен быть обрамлён одинарными кавычками. Маска форматирования указывает какие лементы даты должы быть выбраны и как отображать названия элементов даты: полные названия или аббревиатуры. Названия дней и месяцев автоматически разделяются пробелом. Такое поведение можно изменить, используя параметр маски fill mode (fm). Указав в начале маски параметр fm, вы укажете Oracle о необходимости убрать все пробелы. Доступно много опций для маски форматирования, часть из которых отображена в таблице 10-5. Рассмотрим три запроса

Если текущая системная дата 3 января 2009 года и по умолчанию формат отображения DD/MON/RR тогда запрос один вернёт строку ‘03/JAN/09 is todays date’. Во втором запросе обратите внимание на две детали: во-первых, только месяц выбирается из даты, и во-вторых так как маска форматирования регистрозавсимая и в запросе используется ‘Month’, то запрос вернёт ‘January is a special time’. Нет нужды добавлять пробел в начале литерала, так как функция TO_CHAR автоматически добавит пробел к названию месяца. Если бы маска во втором запросе была ‘MONTH’ то запрос вернул бы ‘JANUARY is a special time’. Параметр fm в третьем запросе препятствует добавлению пробелов и результатом будет ‘Januaryis a special time’. В таблице 10-5 предполагается что обрабатывается дата 2 июня 1975 года и текущий год 2009.

Параметры форматирования, связанные с неделей, кварталом, веком и другими более редко использующимися элементами даты показаны на рисунке 10-7. Столбец результата предполагает, что функция использовалась для работы с датой 24 сентября 1000 года, с маской форматирования указанной в столбец format element.

Компонент время в типе данных дата выбирается, используя модели форматирования в таблице 10-7. Результат рассчитывается функцией TO_CHAR используя дату 27 июня 2010 года время 21:35:13 с маской форматирования указанной в столбце format element.

Некоторые различные элементы, которые можно использовать в форматировании даты и времени перечислены в таблице 10-8. Знаки пунктуации используются для разделения элементов форматирования. Три типа суффиксов существуют для форматирования элементов. Более того, символьные литералы могут быть включены в модель форматирования если они заключены в двойные кавычки. Результаты в таблице 10-8 получены используя функцию TO_CHAR для даты 12 сентября 2008 года 14:31 с маской форматирования указанной в соответствующем столбце.

Рисунок 10-3 – Запрос в таблицу JOB_HISTORY

ORDER BY END_DATE;

Несмотря на то что компонент «век» не отображается по умолчанию, он хранится в базе данных и доступен для запроса. Маска форматирования DD-MON-RR используется по умолчанию для ввода значений и отображения. Когда значение добавляется или изменяется если явно неуказан век, то используется век из функции SYSDATE. Формат RR отличается от формата YY и так как RR также использует значение столетия. Влияние значения столетия на формат RR легче понять если рассмотреть следующие принципы

Если две последние цифры текущего года между 0 и 49, а в указанном значении даты две последние цифры года между 50 и 99 то используется предыдущий век. Предположим, что текущая дата 2 июня 2007 года. Значение века для даты 24-JUNE-94 года будет 20
Если две цифры текущего года между 50-99 и указанной даты также между 55 и 99, то возвращается текущий век. Преположим что текущая дата 2 июня 1975 года. Тогда значение века для 24-JUL-94 будет 20.
Если две цифры текущей даты между 50 и 99, а в укащанной дате год между 0 и 49 – то считается следующий век. Предположим, что текущая дата 2 июня 1975 года, тогда для значения 24-JUL-07 значение века будет 21.

Конвертация строки в дату используя функцию TO_DATE

Функция TO_DATE возвращает значение типа данных DATE. Строка, конвертируемая в дату может содержать все или часть компонентов, составляющих тип DATE. Когда строка содержащая только часть компонентов даты преобразуется в дату, Oracle использует значение по умолчанию для составления валидного значения типа DATE. Части строки сопоставляются с элементами даты используя маску (или модель) форматирования. Синтаксис функции TO_DATE(string1, [format], [nls_parameter]).

Только параметра string1 обязателен, и eсли маска форматирования не указана, string1 должна быть в формат неявно конвертируемом в дату. Необязательный параметр format используется практически всегда и должен быть заключён в одинарные кавычки. Маска форматирования идентича перечисленным в таблицах 10-5, 10-6, и 10-7. У функции TO_DATE есть модификатор fx, которые используется подобно параметру fm функции TO_CHAR. Параметр fx требует обязательного совпадения строки и маски форматирования. Если строка не совпадает с маской – возвращается ошибка. Рассмотрим несколько примеров

Конвертация строки в число используя функцию TO_NUMBER

Функция TO_NUMBER возвращает значение типа данных NUMBER. Исходная строка должна быть составлена таким образом, чтобы все несовместимые символы отсутствовали или были указаны в соответствующей маске форматирования. Синтаксис функции TO_NUMBER(string1, [format], [nls_parameter]). Только string1 является обязательным параметром, и если не указан параметр format то значение должно быть таким, чтобы была возможность неявно сконвертировать его в число. Маски форматирования идентичны перечисленным в таблице 10-4. Рассмотрим запросы

Запрос один не может неявно преобразовать строку, так как она содержит знак валюты и разделители, которые явно неуказаны в маске, поэтому возвращается ошибка ORA-1722: invalid number. Запрос два находит символ валюты, запятой и точки в маске форматирования и несмотря на то что длина маски больше чем чем исходное значение в строке, возвращается число 1000.55

Функция TO_NUMBER конвертирует значение строки в число. Если вы используете число длиннее чем маска, возвращается ошибка. Если вы конвертируете число используя более длинную маску – возвращается значение исходной длины. Не путайте TO_NUMBER с TO_CHAR. Например TO_NUMBER(‘123.45’,’999.9’) вернёт ошибку, когда TO_CHAR(123.45,’999.9’) вернёт 123.6

У меня есть столбец, DECIMAL(9,6) т.е. он поддерживает такие значения, как 999,123456.

Но когда я вставляю данные типа 123,4567, они становятся 123,456700

Как убрать эти нули?

Пожалуйста, измените правильный ответ на ответ @ Andomar @dangalg: Я твердо верю, что это действительно нужно делать на уровне представления. Вот почему я так и не изменил принятый ответ. Однако после дальнейших размышлений я думаю, что должен принять то, что четко указало сообщество, какой ответ является лучшим.

A decimal(9,6) хранит 6 цифр справа от запятой. Отображать ли завершающие нули или нет - это решение форматирования, обычно реализуемое на стороне клиента.

Но поскольку SSMS форматирует float без конечных нулей, вы можете удалить конечные нули, приведя decimal к float :

(Мой десятичный разделитель - это запятая, но SSMS форматирует десятичное число с точкой. Очевидно, это известная проблема .)

+1 Я думал, что преобразование в float внесет некоторую неточность в результаты, но, похоже, все работает нормально. Одним из недостатков этого метода является то, что если вы начнете с «2.0», он превратится в «2». Вероятно, это нормально для человека, задающего вопрос, но мне нужно было сохранить один ноль после десятичной дроби, не оставляя никаких других конечных нулей. Ответ @ user1959416 решает это. Плюс float в целом - очень плохой выбор для хранения чисел. Вы получите ошибки округления, так как это не точный тип. Никогда не используйте поплавок. Комментарий о форматировании чисел с плавающей запятой без конечных нулей был чрезвычайно полезен Правильно ли я думаю, что масштаб и точность десятичной дроби могут превышать масштаб и точность числа с плавающей запятой, и, следовательно, могут быть случаи, когда (со значащими более 17 цифрами) этот ответ не сработает?

Вы можете использовать FORMAT() функцию (SqlAzure и Sql Server 2012+):

Будьте осторожны при использовании с FLOAT (или REAL): не используйте g17 или больше ( g8 или больше с REAL), потому что ограниченная точность машинного представления вызывает нежелательные эффекты:

Кроме того, обратите внимание, что согласно документации :

Также работает в SqlAzure.

Для моих целей я нашел строку формата g8, отформатировавшую мое число как «1e-08», что было не тем, что мне было нужно. Этот ответ действительно привел меня к тому, что я мог бы использовать, что происходит, когда число выглядит как «123.10705000000»? Я пробовал с помощью SELECT CONVERT (DOUBLE PRECISION, 123.10705000000), но он дает мне «123.107» в качестве ответа. и я хочу, чтобы на выходе было "123.10705"? Там в любом случае? Я не хочу использовать CHARINDEX.

Я не хотел приводить к типу float из-за того, что в моей десятичной дроби может быть больше цифр, чем может представлять float

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

Конечно, это обескураживающая практика, когда уровень данных выполняет форматирование (но в моем случае другого слоя нет; пользователь буквально запускает хранимую процедуру и отправляет результат по электронной почте: /)

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