我需要確定給定的字符串是否可以在SQL語句中解釋為數字（整數或浮點數）。如下所示：SELECT AVG(CASE WHEN x ~ '^[0-9]*.?[0-9]*$' THEN x::float ELSE NULL END) FROM test我發現Postgres的模式匹配可用于此目的。所以我適應中給出的聲明這個地方納入浮點數。這是我的代碼：WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'), ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'))SELECT x , x ~ '^[0-9]*.?[0-9]*$' AS isnumericFROM test;輸出： x | isnumeric ---------+----------- | t . | t .0 | t 0. | t 0 | t 1 | t 123 | t 123.456 | t abc | f 1..2 | f 1.2.3.4 | f(11 rows)如您所見，前兩個項目（空字符串''和唯一句點'.'）被錯誤分類為數字類型（不是）。目前，我無法對此進一步了解。任何幫助表示贊賞！更新基于這個答案（和評論），我適應的模式：WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'), ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))SELECT x , x ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$' AS isnumericFROM test;這使： x | isnumeric ----------+----------- | f . | f .0 | t 0. | t 0 | t 1 | t 123 | t 123.456 | t abc | f 1..2 | f 1.2.3.4 | f 1x234 | f 1.234e-5 | f(13 rows)正如我現在看到的那樣，科學計數法和負數仍然存在一些問題。
3 回答
慕碼人8056858
TA貢獻1803條經驗 獲得超6個贊
您可能會注意到，基于正則表達式的方法幾乎不可能正確執行。例如，您的測試說這1.234e-5不是有效數字，而實際上是。另外，您錯過了負數。如果某些東西看起來像數字，但是當您嘗試存儲它會導致溢出怎么辦？
相反，我建議創建試圖實際轉換為的函數NUMERIC（或FLOAT如果您的任務需要它）并返回TRUE或FALSE取決于此轉換是否成功的函數。
此代碼將完全模擬功能ISNUMERIC()：
CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$
DECLARE x NUMERIC;
BEGIN
x = $1::NUMERIC;
RETURN TRUE;
EXCEPTION WHEN others THEN
RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;
在您的數據上調用此函數將得到以下結果：
WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),
('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))
SELECT x, isnumeric(x) FROM test;
x | isnumeric
----------+-----------
| f
. | f
.0 | t
0. | t
0 | t
1 | t
123 | t
123.456 | t
abc | f
1..2 | f
1.2.3.4 | f
1x234 | f
1.234e-5 | t
(13 rows)
如果數據實際上是數字，它不僅更正確，更容易閱讀，而且還可以更快地工作。
呼如林
TA貢獻1798條經驗 獲得超3個贊
我想可能會有這樣的看法（這不是對異常處理的濫用），但總的來說，我認為應該為此使用異常處理機制。測試字符串是否包含數字是正常處理的一部分，而不是“例外”。
但是您對不處理指數是正確的。這是正則表達式（下）的第二個刺。我必須追求使用正則表達式的解決方案的原因是，當遇到錯誤時給出指令退出時，此處提供為“正確”解決方案的解決方案將失敗：
SET exit_on_error = true;
當運行SQL腳本組時，并且如果有任何問題/錯誤我們想立即停止時，我們經常使用它。給出此會話指令后，即使沒有遇到“真實”異常，調用isnumeric的“正確”版本也會導致腳本立即退出。
create or replace function isnumeric(text) returns boolean
immutable
language plpgsql
as $$
begin
if $1 is null or rtrim($1)='' then
return false;
else
return (select $1 ~ '^ *[-+]?[0-9]*([.][0-9]+)?[0-9]*(([eE][-+]?)[0-9]+)? *$');
end if;
end;
$$;
智慧大石
TA貢獻1946條經驗 獲得超3個贊
您的問題是小數點兩側的兩個0或多個[0-9]元素。您需要|在數字標識行中使用邏輯或：
~'^([0-9]+\.?[0-9]*|\.[0-9]+)$'
這將僅排除小數點作為有效數字。
- 3 回答
- 0 關注
- 1455 瀏覽
添加回答
舉報
0/150
提交
取消