postgresql 使用比较运算符比较 postgres 中的字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40601324/
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
Comparing strings in postgres using comparison operators?
提问by bakamike
In many programming languages you can compare strings using operators like >, >=, < etc...and the language will base the comparison on the position of the letter in the alphabet.
在许多编程语言中，您可以使用 >、>=、< 等运算符来比较字符串，并且该语言将根据字母在字母表中的位置进行比较。
For example in PHP
例如在 PHP 中
if ('a' < 'b') {
echo 'Yes';
} else {
echo 'No';
}
> Yes
However in postgres or mysql
但是在 postgres 或 mysql 中
SELECT
CASE WHEN 'a' < 'b' THEN 'yes' END
FROM table
Output: null
I have a table with strings that I need to compare against each other through SQL.
我有一个包含字符串的表，需要通过 SQL 相互比较。
For example: 6.2(5a) 6.2(5b) -- this would be greater than 6.2(5a) Or 6.2(15) -- this would be greater than 6.2(5a)
例如： 6.2(5a) 6.2(5b) -- 这将大于 6.2(5a) 或 6.2(15) -- 这将大于 6.2(5a)
I thought of assigning a number to a letter using a regexp but then that would break the comparisons when there are no letter.
我想过使用正则表达式为字母分配一个数字，但是当没有字母时，这会破坏比较。
How would you go about this purely in SQL?
您将如何纯粹在 SQL 中解决这个问题？
回答by Schwern
NOTE: The original answer went off on a red herring.
注意：最初的答案是在一个红鲱鱼上。
A simple comparison sorts character by character.
一个简单的比较按字符排序。
select 'a1' < 'a9'; -- true because 'a' = 'a' and '1' < '9'.
...but quickly goes to pot.
......但很快就去锅了。
select 'a10' < 'a9'; -- also true for the same reason.
What you want is a natural sortwhere the string parts are compared as strings and the numbers are compared as numbers. Doing a natural sort in SQL is not the easiest thing. You either need fixed field widths to sort each substring separately, or maybe something with regexes...
您想要的是一种自然排序，其中将字符串部分作为字符串进行比较，并将数字作为数字进行比较。在 SQL 中进行自然排序并不是最简单的事情。您需要固定的字段宽度来分别对每个子字符串进行排序，或者可能需要使用正则表达式...
Fortunately there's pg_natural_sort_order, a Postgres extension that implements an efficient natural sort.
幸运的是pg_natural_sort_order是一个 Postgres 扩展，它实现了一种高效的自然排序。
If you can't install extensions you can use a stored procedure like btrsortby 2kan.
如果你不能安装扩展，你可以使用像btrsortby 2kan这样的存储过程。
CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$
SELECT
CASE WHEN ~ '^[^0-9]+' THEN
COALESCE( SUBSTR( , LENGTH(SUBSTRING( FROM '[^0-9]+'))+1 ), '' )
ELSE
COALESCE( SUBSTR( , LENGTH(SUBSTRING( FROM '[0-9]+'))+1 ), '' )
END
$$ LANGUAGE SQL;
CREATE FUNCTION btrsort(text) RETURNS text AS $$
SELECT
CASE WHEN char_length()>0 THEN
CASE WHEN ~ '^[^0-9]+' THEN
RPAD(SUBSTR(COALESCE(SUBSTRING( FROM '^[^0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit())
ELSE
LPAD(SUBSTR(COALESCE(SUBSTRING( FROM '^[0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit())
END
ELSE
END
;
$$ LANGUAGE SQL;
Though it doesn't provide a comparison operator and I'm not going to pretend to understand it. This allows you to use it in an order by.
虽然它不提供比较运算符，我也不会假装理解它。这允许您在order by.
select * from things order by btrsort(whatever);
To prevent your naturally sorted queries from turning to mud on large tables, you can create a btree index on the result of that function.
为了防止自然排序的查询在大表上变成泥，您可以在该函数的结果上创建一个 btree 索引。
create index things_whatever_btrsort_idx ON things( btrsort(whatever) );
SELECT CASE WHEN 'a' < 'b' THEN 'yes' END FROM table Output: null
SELECT CASE WHEN 'a' < 'b' THEN 'yes' END FROM table Output: null
This will only output nothing if the table is empty. You don't need a table to test select statements.
如果表为空，这只会输出任何内容。您不需要表来测试选择语句。
SELECT
CASE WHEN 'a' < 'b' THEN 'yes' END -- yes
