Search using between no postrgres

Question

I am performing a database search in order to return only the characteristics that are between the range of 40 and 100. But when running the script below it is returning me other features that are not related to the searched. Apparently this is happening because the values 6.7 and 8.7 are being considered as 67 and 87 thus staying between the range of 40 and 100.

      SELECT carlis_nome 
        FROM caracteristica
           JOIN caracteristica_lista 
             ON caracteristica_lista.caracteristica_id = caracteristica.caracteristica_id
        WHERE caracteristica.caracteristica_id = 11
            AND CASE 
                WHEN caracteristica_lista.carlis_nome ~ '^[0-9]'
                    THEN TO_NUMBER(caracteristica_lista.carlis_nome, '999999999')
                ELSE 0
                END BETWEEN 40
                AND 100

By running this SQL you are returning these records to me:

Answer 1

You are converting the values to an integer so that postgres is ignoring the separator. Change the function TO_NUMBER for:

TO_NUMBER(caracteristica_lista.carlis_nome, '9.99999999')

And you must get the expected result.