SQL Oracle - 选择字段包含小写字符的位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/317576/
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
Oracle - Select where field has lowercase characters
提问by BrianH
I have a table, users, in an Oracle 9.2.0.6 database. Two of the fields are varchar - last_name and first_name.
我在 Oracle 9.2.0.6 数据库中有一个表 users。其中两个字段是 varchar - last_name 和 first_name。
When rows are inserted into this table, the first name and last name fields are supposed to be in all upper case, but somehow some values in these two fields are mixed case.
当行插入到这个表中时，名字和姓氏字段应该都是大写的，但不知何故这两个字段中的某些值是大小写混合的。
I want to run a query that will show me all of the rows in the table that have first or last names with lowercase characters in it.
我想运行一个查询，该查询将向我显示表中名字或姓氏带有小写字符的所有行。
I searched the net and found REGEXP_LIKE, but that must be for newer versions of oracle - it doesn't seem to work for me.
我在网上搜索并找到了 REGEXP_LIKE，但这一定适用于较新版本的 oracle - 它似乎对我不起作用。
Another thing I tried was to translate "abcde...z" to "$$$$$...$" and then search for a '$' in my field, but there has to be a better way?
我尝试的另一件事是将“abcde...z”翻译为“$$$$$...$”，然后在我的领域中搜索“$”，但必须有更好的方法吗？
Thanks in advance!
提前致谢！
回答by BQ.
How about this:
这个怎么样：
select id, first, last from mytable
where first != upper(first) or last != upper(last);
回答by BrianH
I think BQ's SQL and Justin's second SQL will work, because in this scenario:
我认为 BQ 的 SQL 和 Justin 的第二个 SQL 会起作用，因为在这种情况下：
first_name last_name
---------- ---------
bob johnson
Bob Johnson
BOB JOHNSON
I want my query to return the first 2 rows.
我希望我的查询返回前 2 行。
I just want to make sure that this will be an efficient query though - my table has 500 million rows in it.
我只是想确保这将是一个有效的查询 - 我的表中有 5 亿行。
When you say upper(first_name) != first_name, is "first_name" always pertaining to the current row that oracle is looking at? I was afraid to use this method at first because I was afraid I would end up joining this table to itself, but they way you both wrote the SQL it appears that the equality check is only operating on a row-by-row basis, which would work for me.
当您说 upper(first_name) != first_name 时，“first_name”是否始终与 oracle 正在查看的当前行有关？一开始我很害怕使用这种方法，因为我担心我最终会将这个表加入到它自己中，但是你们俩编写 SQL 的方式似乎是相等性检查仅在逐行的基础上运行，这会对我来说有效。
回答by Sarath Avanavu
If you are looking for Oracle 10g or higher you can use the below example. Consider that you need to find out the rows where the any of the letter in a column is lowercase.
如果您正在寻找 Oracle 10g 或更高版本，您可以使用以下示例。考虑到您需要找出列中任何字母为小写的行。
Column1
.......
MISS
miss
MiSS
In the above example, if you need to find the values missand MiSS, then you could use the below query
在上面的例子中，如果你需要找到值missand MiSS，那么你可以使用下面的查询
SELECT * FROM YOU_TABLE WHERE REGEXP_LIKE(COLUMN1,'[a-z]');
回答by Quyen ht
Try this:
尝试这个：
SELECT * FROM YOU_TABLE WHERE REGEXP_LIKE(COLUMN1,'[a-z]','c'); => Miss, miss lower text
SELECT * FROM YOU_TABLE WHERE REGEXP_LIKE(COLUMN1,'[A-Z]','c'); => Miss, MISS upper text
回答by Piyush K
SELECT *
FROM mytable
WHERE FIRST_NAME IN (SELECT FIRST_NAME
FROM MY_TABLE
MINUS
SELECT UPPER(FIRST_NAME)
FROM MY_TABLE )
回答by Dave
for SQL server where the DB collation setting is Case insensitive use the following:
对于 DB 排序规则设置不区分大小写的 SQL 服务器，请使用以下内容：
SELECT * FROM tbl_user WHERE LEFT(username,1) COLLATE Latin1_General_CS_AI <> UPPER(LEFT(username,1))
