我需要確定給定的字符串是否可以在SQL語句中解釋為數(shù)字（整數(shù)或浮點(diǎn)數(shù)）。如下所示：SELECT AVG(CASE WHEN x ~ '^[0-9]*.?[0-9]*$' THEN x::float ELSE NULL END) FROM test我發(fā)現(xiàn)Postgres的模式匹配可用于此目的。所以我適應(yīng)中給出的聲明這個(gè)地方納入浮點(diǎn)數(shù)。這是我的代碼：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)如您所見，前兩個(gè)項(xiàng)目（空字符串''和唯一句點(diǎn)'.'）被錯(cuò)誤分類為數(shù)字類型（不是）。目前，我無法對(duì)此進(jìn)一步了解。任何幫助表示贊賞！更新基于這個(gè)答案（和評(píng)論），我適應(yīng)的模式：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)正如我現(xiàn)在看到的那樣，科學(xué)計(jì)數(shù)法和負(fù)數(shù)仍然存在一些問題。
3 回答
倚天杖
TA貢獻(xiàn)1828條經(jīng)驗(yàn) 獲得超3個(gè)贊
您可能會(huì)注意到，基于正則表達(dá)式的方法幾乎不可能正確執(zhí)行。例如，您的測(cè)試說這1.234e-5不是有效數(shù)字，而實(shí)際上是。另外，您錯(cuò)過了負(fù)數(shù)。如果某些東西看起來像數(shù)字，但是當(dāng)您嘗試存儲(chǔ)它會(huì)導(dǎo)致溢出怎么辦？
相反，我建議創(chuàng)建試圖實(shí)際轉(zhuǎn)換為的函數(shù)NUMERIC（或FLOAT如果您的任務(wù)需要它）并返回TRUE或FALSE取決于此轉(zhuǎn)換是否成功的函數(shù)。
此代碼將完全模擬功能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;
在您的數(shù)據(jù)上調(diào)用此函數(shù)將得到以下結(jié)果：
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)
如果數(shù)據(jù)實(shí)際上是數(shù)字，它不僅更正確，更容易閱讀，而且還可以更快地工作。
繁星coding
TA貢獻(xiàn)1797條經(jīng)驗(yàn) 獲得超4個(gè)贊
我想可能會(huì)有這樣的看法（這不是對(duì)異常處理的濫用），但總的來說，我認(rèn)為應(yīng)該為此使用異常處理機(jī)制。測(cè)試字符串是否包含數(shù)字是正常處理的一部分，而不是“例外”。
但是您對(duì)不處理指數(shù)是正確的。這是正則表達(dá)式（下）的第二個(gè)刺。我必須追求使用正則表達(dá)式的解決方案的原因是，當(dāng)遇到錯(cuò)誤時(shí)給出指令退出時(shí)，此處提供為“正確”解決方案的解決方案將失?。?/p>
SET exit_on_error = true;
當(dāng)運(yùn)行SQL腳本組時(shí)，并且如果有任何問題/錯(cuò)誤我們想立即停止時(shí)，我們經(jīng)常使用它。給出此會(huì)話指令后，即使沒有遇到“真實(shí)”異常，調(diào)用isnumeric的“正確”版本也會(huì)導(dǎo)致腳本立即退出。
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貢獻(xiàn)1872條經(jīng)驗(yàn) 獲得超4個(gè)贊
您的問題是小數(shù)點(diǎn)兩側(cè)的兩個(gè)0或多個(gè)[0-9]元素。您需要|在數(shù)字標(biāo)識(shí)行中使用邏輯或：
~'^([0-9]+\.?[0-9]*|\.[0-9]+)$'
這將僅排除小數(shù)點(diǎn)作為有效數(shù)字。
- 3 回答
- 0 關(guān)注
- 605 瀏覽
添加回答
舉報(bào)
0/150
提交
取消