Postgres query to check a string is a number
PostgresqlPostgresql Problem Overview
Can anyone tell me the query to check whether a string is a number(double precision). It should return true if the string is number. else it should return false.
consider :
s1 character varying;
s2 character varying;
s1 ='12.41212' => should return true
s2 = 'Service' => should return false
Postgresql Solutions
Solution 1 - Postgresql
I think the easiest way would be a regular expression match:
select '12.41212' ~ '^[0-9\.]+$'
=> true
select 'Service' ~ '^[0-9\.]+$'
=> false
Solution 2 - Postgresql
I would like to propose another suggestion, since 12a345
returns true
by ns16's answer.
SELECT '12.4121' ~ '^\d+(\.\d+)?$'; #true
SELECT 'ServiceS' ~ '^\d+(\.\d+)?$'; #false
SELECT '12a41212' ~ '^\d+(\.\d+)?$'; #false
SELECT '12.4121.' ~ '^\d+(\.\d+)?$'; #false
SELECT '.12.412.' ~ '^\d+(\.\d+)?$'; #false
Solution 3 - Postgresql
I fixed the regular expression that a_horse_with_no_name has suggested.
SELECT '12.41212' ~ '^\d+(\.\d+)?$'; -- true
SELECT 'Service' ~ '^\d+(\.\d+)?$'; -- false
Solution 4 - Postgresql
If you want to check with exponential , +/- . then the best expression is :
^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$
resulting in:
select '12.41212e-5' ~ '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$' ;
as true.
The expression is from: https://www.regular-expressions.info/floatingpoint.html
You can check for other types of numbers, for example if you expect decimal, with a sign.
select '-12.1254' ~ '^[-+]?[0-9]*\.?[0-9]+$';
Solution 5 - Postgresql
I've created a function to check this, using a "try catch".
The function tries to cast the text to "numeric". It returns true if the cast goes right, or return false if the cast fail.
CREATE OR REPLACE FUNCTION "sys"."isnumeric"(text)
RETURNS "pg_catalog"."bool" AS $BODY$
DECLARE x NUMERIC;
BEGIN
x = $1::NUMERIC;
RETURN TRUE;
EXCEPTION WHEN others THEN
RETURN FALSE;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 100
;
ALTER FUNCTION "sys"."isnumeric"(text) OWNER TO "postgres";
Solution 6 - Postgresql
select s1 ~ '^\d+$';
select s2 ~ '^\d+$';
Solution 7 - Postgresql
If you only need to accept double precision numbers, this should work fine:
select '12.41212' ~ '^\d+\.?\d+$'; -- true
select 'Service' ~ '^\d+\.?\d+$'; -- false
This would also accept integers, and negative numbers:
select '-1241212' ~ '^-?\d*\.?\d+$'; -- true