oracle 如何检查字段是字符还是数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16869525/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to check a field is char or number?
提问by R.S
I have a NVARCHAR field in my table. (using Oracle 11g)
我的表中有一个 NVARCHAR 字段。（使用 Oracle 11g）
I want check it has char data or number data.
我想检查它是否有字符数据或数字数据。
help me to write a select query. (using PL-SQL)
帮我写一个选择查询。（使用 PL-SQL）
I think I can write it with case, but I can't implement that. (I have no method for checking isChar and isNumber)
我想我可以用 case 写它，但我无法实现。（我没有检查 isChar 和 isNumber 的方法）
select
case <myField>
when <isChar> then
<this is a char data>
when <IsNum> then
<this is number data>
End
from <myTable>
回答by peterm
Using REGEXP_LIKE
使用 REGEXP_LIKE
SELECT t.*,
CASE
WHEN REGEXP_LIKE(column1, '^[0-9]+$') THEN 'Numeric'
ELSE 'Char'
END data_type
FROM table1 t;
And without it
没有它
SELECT t.*,
CASE
WHEN LENGTH(TRIM(TRANSLATE (column1, '0123456789',' '))) IS NULL THEN 'Numeric'
ELSE 'Char'
END data_type
FROM table1 t;
Here is SQLFiddledemo
这是SQLFiddle演示
Both versions can be adjusted to accommodate period, plus and minus signs if needed
如果需要，两个版本都可以调整以适应句号、加号和减号
UPDATEAs @tbone correctly commented these queries don't take into account NULLs and return 'Char' and 'Numeric' as data_type respectively when a value of column1 is NULL.
更新正如@tbone 正确评论NULL的那样，当 column1 的值为 时，这些查询不考虑s 并分别返回“Char”和“Numeric”作为数据类型NULL。
There are at least two options how to deal with it:
如何处理它至少有两种选择：
FirstSimply filter out rows with NULLvalues if we are not interested in them
首先，NULL如果我们对它们不感兴趣，只需过滤掉带有值的行
...
WHERE column1 IS NOT NULL
SecondIntroduce NULL(obviously it can be 'N/A' or something else) data type in CASE
第二次引入NULL（显然它可以是“N/A”或其他）数据类型CASE
SELECT t.*,
CASE
WHEN column1 IS NULL THEN NULL
ELSE CASE
WHEN REGEXP_LIKE(column1, '^[0-9]+$') THEN 'Numeric'
ELSE 'Char'
END
END data_type
FROM table1 t
Here is updated SQLFiddledemo
这是更新的SQLFiddle演示
回答by Ankur Trapasiya
Try this SQL. I have assumed in case statement there is character data.
试试这个 SQL。我假设在 case 语句中有字符数据。
select case <myField>
when (ascii(char)>=65 and ascii(char)<=90) OR (ascii(char)>=97 and ascii(char)<=122)
then
<this is a char data>
when ascii(char)>=48 and ascii(char)<=57
then
<this is number data>
End
from <myTable>
Hope this helps.
希望这可以帮助。
