Thursday, January 17, 2013

Postgresql function to check a value is number

1 comments
In Postrgesql, I created a function to check a column value is number or not. Its a simple function.
CREATE OR REPLACE FUNCTION is_number(prm_str text) RETURNS BOOLEAN AS $$
DECLARE
  v_return BOOLEAN;
BEGIN
  IF regexp_matches(prm_str,E'^-*[[:digit:]]+\.?[[:digit:]]+$') is not null
  THEN
     v_return = TRUE;
  ELSE
    v_return = FALSE;
  END IF;
  RETURN v_return;    
END;
$$ LANGUAGE 'plpgsql';

Now execute the function:
 SELECT *, is_number(col_val) as isNumber FROM table;

One Response so far

  1. It doesn't work if you put a single digit MMLPQP

Leave a Reply

Labels