Search using between no postrgres

Navigation
0

I am performing a search in the database with the purpose of returning only the features that are between the range of 40 and 100. But when I run the script below it is returning me other features that are not related to the searched. Apparently this is occurring because the values 6.7 and 8.7 are being considered as 67 and 87 thus getting 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

When executing this SQL is returning me these records:

    
asked by anonymous 29.10.2015 / 17:49

1 answer

1

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

TO_NUMBER(caracteristica_lista.carlis_nome, '9.99999999')

And you should get the expected result.

    
29.10.2015 / 18:05
Separate access hits by area in Google Analytics Update a div with sql data without reloading the page