筛选列SQL中仅数字数据的行 [英] Filter the rows with number only data in a column SQL

问题描述

我试图通过应用标识仅数字列的过滤条件来SELECT表中的行.这是一个仅报告的查询，因此，我们不必担心性能，因为我们没有编译PL/SQL的特权，无法通过TO_NUMBER()进行检查并返回是否为数字.

I am trying to SELECT rows in a table, by applying a filter condition of identifying number only columns. It is a report only query, so we least bother the performance, as we dont have the privilege to compile a PL/SQL am unable to check by TO_NUMBER() and return if it is numeric or not.

我必须在SQL中实现它.同样，该列具有这样的值，必须将其视为数字.

I have to achieve it in SQL. Also the column is having the values like this, which have to be treated as Numbers.

-1.0
-0.1
-.1
+1,2034.89
+00000
1023

经过开创性的研究，我写了这本书.(很难)

After ground breaking research, I wrote this.(Hard time)

  WITH dummy_data AS
  ( SELECT '-1.0' AS txt FROM dual
  UNION ALL
  SELECT '+0.1' FROM dual
  UNION ALL
  SELECT '-.1' FROM dual
  UNION ALL
  SELECT '+1,2034.89.00' FROM dual
  UNION ALL
  SELECT '+1,2034.89' FROM dual
  UNION ALL
  SELECT 'Deva +21' FROM dual
  UNION ALL
  SELECT '1+1' FROM dual
  UNION ALL
  SELECT '1023' FROM dual
  )
SELECT dummy_data.*,
  REGEXP_COUNT(txt,'.')
FROM dummy_data
WHERE REGEXP_LIKE (TRANSLATE(TRIM(txt),'+,-.','0000'),'^[-+]*[[:digit:]]');

我明白了

TXT           REGEXP_COUNT(TXT,'.')
------------- ---------------------
-1.0                              4
+0.1                              4
-.1                               3
+1,2034.89.00                    13 /* Should not be returned */
+1,2034.89                       10
1+1                               3 /* Should not be returned */
1023                              4

7 rows selected.

现在非常困惑2个问题.

Now terribly confused with 2 Questions.

1)结果我也得到了+1,2034.89.00，我应该将其消除. (意味着，两个小数点)不仅是小数点，还应消除所有其他特殊字符(-+，)的两倍)

2)为了使其更难看，计划要做一个REGEXP_COUNT('.') <= 1.但这并没有返回我的期望，选择它时，我看到返回了奇怪的值.

1) I get +1,2034.89.00 too in result, I should eliminate it. (means, two decimal points) Not just decimal point, double in every other special character (-+,) should be eliminated)

2) To make it uglier, planned to do a REGEXP_COUNT('.') <= 1. But it is not returning my expectation, while selecting it, I see strange values returned.

有人可以帮我设计REGEXP以避免('.','+','-')的重复出现

Can someone help me to frame the REGEXP for the avoiding the double occurences of ('.','+','-')

推荐答案

我只是想纠正您的错误，并使SQL尽可能简单.但不是整齐！

I just tried to correct the mistakes of you and made the SQL simple as possible. But not neat!

WITH dummy_data AS
  ( SELECT '-1.0' AS txt FROM dual
  UNION ALL
  SELECT '+.0' FROM dual
  UNION ALL
  SELECT '-.1' FROM dual
  UNION ALL
  SELECT '+1,2034.89.0' FROM dual
  UNION ALL
  SELECT '+1,2034.89' FROM dual
  UNION ALL
  SELECT 'Deva +21' FROM dual
  UNION ALL
  SELECT 'DeVA 234 Deva' FROM dual
  UNION ALL
  SELECT '1023' FROM dual
  )
SELECT to_number(REPLACE(txt,',')),
  REGEXP_COUNT(txt,'.')
FROM dummy_data
WHERE REGEXP_LIKE (txt,'^[-+]*')
AND NOT REGEXP_LIKE (TRANSLATE(txt,'+,-.','0000'),'[^[:digit:]]')
AND REGEXP_COUNT(txt,',')  <= 1
AND REGEXP_COUNT(txt,'\+') <= 1
AND REGEXP_COUNT(txt,'\-') <= 1
AND REGEXP_COUNT(txt,'\.') <= 1;

这篇关于筛选列SQL中仅数字数据的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！