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
I think the easiest way would be a regular expression match:
select '12.41212' ~ '^[0-9\.]+$'
=> true
select 'Service' ~ '^[0-9\.]+$'
=> false
nonstandard use of escape in a string literal. Also, i had to support - or + as prefixes...so this solved those as well: select '-12.41212' ~ E'^([-+])?[0-9\.]+$';
'1E3'.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
I fixed the regular expression that a_horse_with_no_name has suggested.
SELECT '12.41212' ~ '^\d+(\.\d+)?$'; -- true
SELECT 'Service' ~ '^\d+(\.\d+)?$'; -- false
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";
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]+$';
select s1 ~ '^\d+$';
select s2 ~ '^\d+$';
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
Building on Fernando's answer, this is a tiny bit shorter, does not need a variable to be declared and gives the exact exception type (for educational purposes):
create or replace function isnumeric(string text) returns bool as $$
begin
perform string::numeric;
return true;
exception
when invalid_text_representation then
return false;
end;
$$ language plpgsql security invoker;
