Compare numerically in varchar column

I need to write HQL query (not native SQL if possible) on PostgreSQL db that selects the rows that have attr value greater than 39. The problem is that attr column is a varchar column and contains not only numbers. I need only the numbers and only greater than 39. Native query that works for me is as follows:

SELECT * FROM my_table WHERE attr ~ '^[0-9\.]+$' AND CAST(attr AS BIGINT) > 39;

I tried following HQL equivalents (none of which works):

1. SELECT myTable FROM MyTable myTable WHERE attr ~ '^[0-9\.]+$' AND CAST(attr AS long) > 39; //~ cannot be used in HQL query
2. SELECT myTable FROM MyTable myTable WHERE ISNUMERIC(attr) = 1 AND CAST(attr AS long) > 39; //cannot extract result set
3. SELECT myTable FROM MyTable myTable WHERE attr LIKE '^[0-9\.]+$' AND CAST(attr AS long) > 39; //cannot extract result set
4. SELECT myTable FROM MyTable myTable WHERE CAST(attr AS long) > 39; //cannot extract result set
5. SELECT myTable FROM MyTable myTable WHERE attr > '39'; //wrong data types - rows with 4 in the attr column are found

Solution

Try to create a PostgreSQL function like this one:

CREATE FUNCTION parse_attr_to_boolean(attr character varying) RETURNS BOOLEAN AS $$
        BEGIN
                RETURN attr ~ '^[0-9\.]+$' AND CAST(attr AS BIGINT) > 39;
        END;
$$ LANGUAGE plpgsql;

Now, you can just call the function in your HQL query:

SELECT t 
FROM MyTable t 
WHERE parse_attr_to_boolean(t.attr)

You only need to register the SQL function, like parse_attr_to_boolean, if the function is used in the SELECT clause. However, since you are using it in the WHERE clause, it can just be passed as-is to the SQL query.