Order varchar string as numeric

sql

data-conversion

sql-indexing

database-performance

byNikita Barsukov·Sep 3, 2024

Quickly sort VARCHAR values numerically by casting them to numeric types. For integers, use:

ORDER BY CAST(your_column AS INT)

For decimals, utilize:

ORDER BY CAST(your_column AS DECIMAL(10, 2))

Just don't forget to substitute your_column with your actual column name.

Scrubbing out non-numeric characters

When your VARCHAR column includes pesky non-numeric characters, roll up your sleeves and sanitize the input before casting:

ORDER BY NULLIF(regexp_replace(your_column, '\D', '', 'g'), '')::int

The regexp_replace function is like your digital broom, sweeping out non-digit characters (\D pattern), enabling your CAST to an INT to work seamlessly.

Is it a valid integer?

Before running headfirst into casting, first ensure the values are valid integers to keep runtime errors at bay:

ORDER BY CASE 
           WHEN pg_input_is_valid(your_column, 'integer') THEN your_column::int 
           ELSE default_value 
         END

When in doubt, pg_input_is_valid() function is your bouncer, ensuring only legitimate integers get through.

Speed racer: Use indexes wisely

Facing a large dataset? Consider putting on performance booster, and create an index:

CREATE INDEX index_name ON your_table ((CAST(your_column AS INT)));

Run a EXPLAIN ANALYZE as your post-creation test drive, ensuring your database is using the new index.

When in Rome: Converting to integer column

Continual need for numerical sorting? Make life easier, morph these fields to integers:

ALTER TABLE your_table ALTER COLUMN your_column TYPE INT USING your_column::int;

Trust me, your performance will thank you.

Tricky scenarios when casting

Decimal juggling

Juggling with decimal numbers? Cast to a double precision life-buoy:

ORDER BY CAST(your_column AS DOUBLE PRECISION)

Precision protected, sorted accurately. Winning at life right here.

Mixed content chaos

When your column is a mix of integers and decimals, brace for a ride, but remember to brace your typecasting:

ORDER BY CASE 
           WHEN your_column ~ '^\d+\.\d+$' THEN CAST(your_column AS DOUBLE PRECISION) -- Just your everyday Regex wizardry!
           WHEN your_column ~ '^\d+$' THEN CAST(your_column AS INT) -- Integers, you're caught!
           ELSE default_value  -- Default to a safety net, just to keep things rolling.
         END

On-the-fly adaptation

Life doesn't give you data conversions? Do it yourself!

SELECT *, CAST(NULLIF(regexp_replace(your_column, '\D', '', 'g'), '') AS INT) as numeric_column -- Could we BE any more sanitized?
FROM your_table
ORDER BY numeric_column;