Преобразование строки в десятичный SQL Redshift

Question

У меня есть этот SQL-запрос красного смещения. Я извлек число из десятичного числа из комментария, используя функцию "REGEXP_SUBSTR". Мне также нужно преобразовать его из строки в число / десятичное число. Затем мне нужно вычесть это число из общего числа.

Это мой запрос

SELECT sc.comment, 
       sm.subtotal, 
       to_number(REGEXP_SUBSTR(sc.comment, '[0.00-9]+..[0.00-9]+', 1),'9999999D99')

FROM "sales_memo_comments" sc INNER JOIN "sales_memo" sm ON sc.foreign_id = sm.parent_id

Я попытался использовать функцию "to_number" в Redshift SQL, но она выдала мне следующее: ОШИБКА: неверный синтаксис ввода для типа numeric: " "

Это текущий вывод Перед извлечением суммы возврата номера из столбца комментариев:

comment
"SAR719.00 Refund transaction executed successfully, Refund Request ID:504081288877953603 \n    , Authorization Code:095542 "
"AUD52.07 Refund transaction executed successfully, Refund Request ID:6J45695858A90833"
Canceled by : [e-mail]
refund amount is [MYR197.41]
"Please Ignore Order refunded by Refund Request ID:5002758809696048 , Authorization Code:2587759"
OMR37.83($98.23) Refund transaction executed successfully

Это все после использования вышеупомянутого SQL-запроса с REGEXP. Я все еще получаю некоторые аномалии.

comment
719
52.07
.co.
197.41
5.0027621
37.83($98.23

Два вопроса

1. Как мне отредактировать REGEXP, чтобы учесть аномалии, замеченные выше
2. Как мне преобразовать мою строку REGEXP в числовое значение, чтобы вычитать другой числовой столбец?

Любая помощь будет оценена.

Answer 1

Мой подход заключается в том, чтобы сначала добавить два столбца: один для длины строки, а другой - для подсчета разрешенных символов. Из этой таблицы вы можете отфильтровать только те строки, в которых совпадают два (т. Е. Нет недопустимых символов), а затем просто преобразовать оставшиеся значения в числа с плавающей запятой или в десятичные числа или что-то еще.

with temp as (
SELECT '719' as comment
UNION SELECT '52.07'
UNION SELECT '.co.'
UNION SELECT '197.41'
UNION SELECT '5.0027621'
UNION SELECT '37.83($98.23'
),
temp2 as (
  SELECT *
    ,regexp_count(comment, '[0-9.]') as good_char_length
    ,len(comment) as str_length
  FROM
    temp
)

SELECT *
  ,comment::float as comment_as_float
FROM
  temp2
WHERE
  good_char_length = str_length

Answer 2

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

create or replace function isnumeric (aval VARCHAR(20000))
  returns bool
IMMUTABLE
as $$
    try:
       x = int(aval);
    except:
       return (1==2);
    else:
       return (1==1);
$$ language plpythonu;

Затем вы можете изменить свой код следующим образом

SELECT sc.comment, 
       sm.subtotal, 
       to_number(
case when isnumeric(REGEXP_SUBSTR(sc.comment, '[0.00-9]+..[0.00-9]+', 1))
then REGEXP_SUBSTR(sc.comment, '[0.00-9]+..[0.00-9]+', 1)
else 0 end
,'9999999D99')

FROM "sales_memo_comments" sc INNER JOIN "sales_memo" sm ON sc.foriegn_id = sm.parent_id