Is it possible to order result rows by a varchar
column cast to integer
in Postgres 8.3?
'LPAD(lower(column_name))' is used to sort the varchar field numerically in MySQL.
So what is varchar in SQL? As the name suggests, varchar means character data that is varying. Also known as Variable Character, it is an indeterminate length string data type. It can hold numbers, letters and special characters.
It's absolutely possible.
ORDER BY varchar_column::int
Be sure to have valid integer literals in your varchar
column for each entry or you get an exception invalid input syntax for integer: ...
. (Leading and trailing white space is ok - it will be trimmed automatically.)
If that's the case, though, then why not convert the column to integer
to begin with? Smaller, faster, cleaner, simpler.
To remove non-digit characters before the cast and thereby avoid possible exceptions:
ORDER BY NULLIF(regexp_replace(varchar_column, '\D', '', 'g'), '')::int
The regexp_replace()
expression effectively removes all non-digits, so only digits remain or an empty string. (See below.)
\D
is shorthand for the character class [^[:digit:]]
, meaning all non-digits ([^0-9]
).
In old Postgres versions with the outdated setting standard_conforming_strings = off
, you have to use Posix escape string syntax E'\\D'
to escape the backslash \
. This was default in Postgres 8.3, so you'll need that for your outdated version.
The 4th parameter g
is for "globally", instructing to replace all occurrences, not just the first.
You may want to allow a leading dash (-
) for negative numbers.
If the the string has no digits at all, the result is an empty string which is not valid for a cast to integer
. Convert empty strings to NULL
with NULLIF
. (You might consider 0
instead.)
The result is guaranteed to be valid. This procedure is for a cast to integer
as requested in the body of the question, not for numeric
as the title mentions.
One way is an index on an expression.
CREATE INDEX tbl_varchar_col2int_idx ON tbl (cast(NULLIF(regexp_replace(varchar_column, '\D', '', 'g'), '') AS integer));
Then use the same expression in the ORDER BY
clause:
ORDER BY cast(NULLIF(regexp_replace(varchar_column, '\D', '', 'g'), '') AS integer)
Test with EXPLAIN ANALYZE
whether the functional index actually gets used.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With