Postgres request to check string is a number

Question

Can someone tell me a request to check if a string is a number (double precision). It should return true if the string is a number. otherwise it should return false.

consider:

s1 character varying; s2 character varying; s1 ='12.41212' => should return true s2 = 'Service' => should return false 

Answer 1

I think the easiest way is to regex:

 select '12.41212' ~ '^[0-9\.]+$' => true select 'Service' ~ '^[0-9\.]+$' => false 

Answer 2

I fixed the regex suggested by a_horse_with_no_name.

 SELECT '12.41212' ~ '^\d+(\.\d+)?$'; #true SELECT 'Service' ~ '^\d+(\.\d+)?$'; #false 

Answer 3

I would like to suggest another suggestion, since 12a345 returns true when ns16 answers.

 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 

Answer 4

If you want to check with exponential, + / -. then the best expression is:

 ^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$ 

as a result:

 select '12.41212e-5' ~ '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$' ; 

as true.

Expression from: https://www.regular-expressions.info/floatingpoint.html

You can check other types of numbers, for example, if you expect a decimal, with a sign.

  select '-12.1254' ~ '^[-+]?[0-9]*\.?[0-9]+$';