Oracle SQL LIKE Operator not working with values less than 1

I am trying to use the LIKE operator with a column name which contains values less than 1..
For example:

• -0.009123
• 1.9504
• 0.240921

The LIKE statement returns a value for any value which is greater than 0 so for example 1.95 but NOT for any value that begins with 0. or -0.

My Oracle SQL Looks like this:

SELECT * FROM planet WHERE location LIKE '0.240921%' doesn’t work

SELECT * FROM planet WHERE location LIKE '1.9577821%' works

The data type for this column in Oracle is: NUMBER (15, 10)

Why does the statement above NOT work for values that start with 0 e.g. 0.20012, -0.029115, 0.25009 etc etc

>Solution :

location is a number and LIKE compares strings so you are not actually comparing location you are implicitly comparing TO_CHAR(location) to '0.240921%'.

You will probably find that the leading 0 is being omitted and need to use:

SELECT * FROM planet WHERE location LIKE '.240921%'

Or explicitly format the number as a string:

SELECT *
FROM   planet
WHERE  TO_CHAR(location, 'fm99990.0000000000') LIKE '0.240921%'

or, don’t compare on strings:

SELECT *
FROM   planet
WHERE  location >= 0.240921
AND    location <  0.240922

fiddle