postgresql 如何判断一个字符是大写还是小写？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13707401/
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 determine if a character is uppercase or lowercase in postgresql?
提问by Pupkov-Zadnij
I have failed to find any function like isupperor islowerin postgresql.
What I actually need is to select all the records from a table, where one of the columns contains capitized (but not uppercase) words. That is, the first symbol of each word is uppercase, and the second is lowercase. Words can be written in any language.
我并没有发现任何类似的功能isupper或islowerPostgreSQL中。我真正需要的是从表中选择所有记录，其中一列包含大写（但不是大写）单词。即每个单词的第一个符号是大写的，第二个是小写的。文字可以用任何语言书写。
回答by BobG
What about just selecting the rows where the case of the first letter in the column is not equal to the lowercase version of the first letter in the column?
只选择列中第一个字母的大小写不等于列中第一个字母的小写版本的行怎么样？
Something like:
就像是：
SELECT * FROM table
WHERE SUBSTRING(col FROM 1 FOR 1) != LOWER(SUBSTRING(col FROM 1 FOR 1))
In theory, the above should take the database charset/locale into account as well.
理论上，上述内容也应考虑数据库字符集/区域设置。
回答by dbenhur
You can use Postgres regexpto test for your specific condition:
您可以使用Postgres regexp来测试您的特定条件：
select * from sample
where col ~ E'^[[:upper:]][^[:upper:]]'
You could use E'^[[:upper:]][[:lower:]]'if the second character must be lowercase alpha instead of any non-uppercase.
E'^[[:upper:]][[:lower:]]'如果第二个字符必须是小写字母而不是任何非大写字母，则可以使用。
回答by Marc
Since postgresqlis case sensitive for string comparisons, BobG answer is better
由于postgresql字符串比较区分大小写，因此 BobG 答案更好
Another solution would be to use asciiwith string functions
Like this
像这样
SELECT *
FROM yourTable
WHERE (ascii(LEFT(yourColumn), 1) BETWEEN 65 AND 90)
AND (ascii(SUBSTRING(yourColumn from 2 for 1), 1) BETWEEN 97 AND 122)
when it's between 65 and 90 it's a capital letter as you can see in the ascii table I linked
当它在 65 到 90 之间时，它是一个大写字母，正如您在我链接的 ascii 表中所见
if it's between 97 and 122 it's lower case
如果它在 97 和 122 之间，则为小写
回答by Kemin Zhou
If you want to know whether a string contains at least one lower case character then you can use the upper function [upper(mystr)=mystr]:
如果你想知道一个字符串是否至少包含一个小写字符，那么你可以使用大写函数 [upper(mystr)=mystr]：
dbname=> select upper('AAbbCC')='AAbbCC';
?column?
----------
f
(1 row)
dbname=> select upper('AABBCC')='AABBCC';
?column?
----------
t
(1 row)
You can use the same logic for checking that a string contains at least one upper case character with the lower() sql function.
您可以使用相同的逻辑通过 lower() sql 函数检查字符串是否至少包含一个大写字符。
For more complicated pattern, you will need to use regular expression or substring as proposed by earlier answers.
对于更复杂的模式，您将需要使用早期答案中提出的正则表达式或子字符串。
