postgresql – Comparing strings in postgres using comparison operators?
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 sort where 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…
Fortunately theres pg_natural_sort_order, a Postgres extension that implements an efficient natural sort.
If you cant install extensions you can use a stored procedure like btrsort by 2kan.
CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$ SELECT CASE WHEN $1 ~ ^[^0-9]+ THEN COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM [^0-9]+))+1 ), ) ELSE COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM [0-9]+))+1 ), ) END $$ LANGUAGE SQL; CREATE FUNCTION btrsort(text) RETURNS text AS $$ SELECT CASE WHEN char_length($1)>0 THEN CASE WHEN $1 ~ ^[^0-9]+ THEN RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM ^[^0-9]+), ), 1, 12), 12, ) || btrsort(btrsort_nextunit($1)) ELSE LPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM ^[0-9]+), ), 1, 12), 12, ) || btrsort(btrsort_nextunit($1)) END ELSE $1 END ; $$ LANGUAGE SQL;
Though it doesnt provide a comparison operator and Im not going to pretend to understand it. This allows you to use it in an
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.
create index things_whatever_btrsort_idx ON things( btrsort(whatever) );
SELECT CASE WHEN a < b THEN yes END FROM table Output: null
This will only output nothing if the table is empty. You dont need a table to test select statements.
SELECT CASE WHEN a < b THEN yes END -- yes