как отфильтровать нечисловые значения в текстовом поле в тераданных?

o У меня есть таблица teradata с примерно 10 миллионами записей, в которой хранится поле числового идентификатора в виде varchar. мне нужно передать значения в этом поле в столбец bigint в другой таблице, но я не могу просто сказать приведение (id_field как bigint), потому что я получаю ошибку недопустимого символа. просматривая значения, я обнаружил, что в любой позиции строки может быть символ, поэтому, скажем, строка varchar (18), я мог бы отфильтровать недопустимые строки следующим образом:

     where substr(id_field,1,1) not in (/*big,ugly array of non-numeric chars*/)
     and substr(id_field,2,1) not in (/*big,ugly array of non-numeric chars*/)

etc, etc...

тогда гипс сработает, но в долгосрочной перспективе это неосуществимо. это медленно, и если строка имеет 18 возможных символов, это делает запрос нечитаемым. как я могу отфильтровать строки со значением в этом поле, которое не будет отображаться как bigint, без проверки каждого символа по отдельности на наличие массива нечисловых символов?

примерные значения будут

   123abc464
   a2.3v65
   a_356087
   ........
   000000000
   BOB KNIGHT
   1235468099

значения не следуют определенным шаблонам, мне просто нужно отфильтровать те, которые содержат ЛЮБЫЕ нечисловые данные. 123456789 подходит, а 123.abc_c3865 нет...


types sql teradata filtering
person Chris Drappier    schedule 24.08.2010    source источник
comment
Пожалуйста, приведите несколько примеров. Вы хотите сказать, что у вас есть такие данные? 123abc456 Тогда вы называете это числовым полем идентификатора?   -  person Wei    schedule 01.02.2011

Ответы (8)


arrow_upward
6
arrow_downward

Начиная с TD14, Teradata добавила некоторые функции, теперь есть несколько способов, например:

WHERE RTRIM(col, '0123456789') = ''

Но самый простой способ — это TO_NUMBER, который возвращает NULL для неверных данных:

TO_NUMBER(col)
person dnoeth    schedule 19.08.2015

arrow_upward
6
arrow_downward

Лучшее, что мне когда-либо удавалось, это:

where char2hexint(upper(id_field)) = char2hexint(lower(id_field))

Поскольку символы верхнего регистра дают другое шестнадцатеричное значение по сравнению с символами нижнего регистра, это гарантирует, что у вас нет буквенных символов, но все равно останутся символы подчеркивания, двоеточия и т. д. Если это не соответствует вашим требованиям, вам может потребоваться написать UDF.

person lins314159    schedule 26.08.2010
comment
К сожалению, мне приходится иметь дело со специальными символами. что такое УДФ? - person Chris Drappier; 26.08.2010
comment
Определяемая пользователем функция, написанная на C. Есть функция для проверки BigInts по адресу developer.teradata.com/blog/madmac/2010/03/ - person lins314159; 27.08.2010
comment
Обновленная ссылка: downloads.teradata.com /блог/madmac/2010/03/ - person Cobra; 26.05.2020

arrow_upward
0
arrow_downward

Можем ли мы также попытаться разделить значения в поле на какое-то целое число, «если разделить, то должно быть число, а если нет, и выдается какая-то ошибка, то должен быть какой-то символ ...». Думаю, это было бы очень быстро, как только математика вовлеченный...

person a_sillyguy    schedule 05.04.2011
comment
проблема с этим подходом заключается в том, что Teradata не сообщает вам, в каких строках есть символы при этом сбое. он просто говорит, что есть сбой. Как найти конкретную строку, чтобы решить проблему? - person Chris Drappier; 28.08.2012

arrow_upward
0
arrow_downward

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

select cast(case when upper(substring('12E'from 1 for 1)) = lower(substring('12E'from 1 for 1)) then substring('12E'from 1 for 1) else null end ||
             case when upper(substring('12E'from 2 for 1)) = lower(substring('12E'from 2 for 1)) then substring('12E'from 2 for 1) else null end ||
             case when upper(substring('12E'from 3 for 1)) = lower(substring('12E'from 3 for 1)) then substring('12E'from 3 for 1) else null end ||
             case when upper(substring('12E'from 4 for 1)) = lower(substring('12E'from 4 for 1)) then substring('12E'from 4 for 1) else null end ||
             case when upper(substring('12E'from 5 for 1)) = lower(substring('12E'from 5 for 1)) then substring('12E'from 5 for 1) else null end ||
             case when upper(substring('12E'from 2 for 1)) = lower(substring('12E'from 2 for 1)) then substring('12E'from 2 for 1) else null end
             as integer)
person rossinaus    schedule 28.08.2012
comment
это похожее решение на принятый ответ. Но та же проблема все еще остается. Как вы обрабатываете специальные символы? - person Chris Drappier; 28.08.2012

arrow_upward
0
arrow_downward

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

WHERE id_Field NOT LIKE '%[^0-9]%'
person Kaveh    schedule 03.11.2014
comment
Teradata не выполняет обработку регулярных выражений. По крайней мере, не та реализация, которой я занимался в то время. Хотя, конечно, было бы намного проще. - person Chris Drappier; 04.11.2014
comment
В TD14 добавлены некоторые функции, основанные на регулярном выражении: WHERE REGEXP_SIMILAR(id_field , '[0-9]') = 1, до TD14 может быть oTRANSLATE: WHERE oTranslate('id_field', '0123456789','') = '' - person dnoeth; 08.11.2014

arrow_upward
0
arrow_downward

Я нашел ответ lins314159 очень полезным в подобной проблеме. Это может быть старый поток, но для чего он стоит, я использовал:

char2hexint(upper(id_field)) = char2hexint(lower(id_field)) И substr(id_field,1,1) IN (от 1 до 9)

чтобы успешно преобразовать оставшиеся результаты VARCHAR в INT

person user3867061    schedule 19.08.2015

arrow_upward
0
arrow_downward 

SELECT customer_id
FROM t
WHERE UPPER(customer_id)(CASESPECIFIC) <>
      LOWER(customer_id)(CASESPECIFIC);

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

person Nabeel Asif    schedule 22.07.2020
comment
Нет, это работает только для символов, в которых существует верхний/нижний регистр: попробуйте 1<23 или 1ß23. - person dnoeth; 22.07.2020

arrow_upward
0
arrow_downward 

SELECT id_field
WHERE oTranslate(id_field, '0123456789','')<>'';

Это хорошо работает для меня! Он показывает любое поле id_field, содержащее нечисловое значение.

person Godwin Gwei    schedule 09.10.2020