Search code examples
sqlsql-serversql-like

'LIKE' issues with FLOAT: SQL query needed to find values >= 4 decimal places

I have a conundrum....

There is a table with one NVARCHAR(50) Float column that has many rows with many numbers of various decimal lengths:

  • '3304.063'
  • '3304.0625'
  • '39.53'
  • '39.2'

I need to write a query to find only numbers with decimal places >= 4

First the query I wrote was:

SELECT
     Column
FROM Tablename
WHERE Column LIKE '%.[0-9][0-9]%'

The above code finds all numbers with decimal places >= 2:

  • '3304.063'
  • '3304.0625'
  • '39.53'

Perfect! Now, I just need to increase the [0-9] by 2...

SELECT
     Column
FROM Tablename
WHERE Column LIKE '%.[0-9][0-9][0-9][0-9]%'

this returned nothing! What?

Does anyone have an explanation as to what went wrong as well and/or a possible solution? I'm kind of stumped and my hunch is that it is some sort of 'LIKE' limitation..

Any help would be appreciated! Thanks.

Solution

  • After your edit, you stated you are using FLOAT which is an approximate value stored as 4 or 8 bytes, or 7 or 15 digits of precision. The documents explicitly state that not all values in the data type range can be represented exactly. It also states you can use the STR() function when converting it which you'll need to get your formatting right. Here is how:

    declare @table table (columnName float)

insert into @table
values
('3304.063'),
('3304.0625'), 
('39.53'),
('39.2')


--see the conversion
select * , str(columnName,20,4)
from @table

--now use it in a where clause. 
--Return all values where the last digit isn't 0 from STR() the conversion
select  *
from @table
where right(str(columnName,20,4),1) != 0

    OLD ANSWER

    Your LIKE statement would do it, and here is another way just to show they both work. 

    declare @table table (columnName varchar(64))

insert into @table
values
('3304.063'),
('3304.0625'),
('39.53'),
('39.2')

select *
from @table
where len(right(columnName,len(columnName) - charindex('.',columnName))) >= 4

select *
from @table
where columnName like '%.[0-9][0-9][0-9][0-9]%'

    One thing that could be causing this is a space in the number somewhere... since you said the column type was VARCHAR this is a possibility, and could be avoided by storing the value as DECIMAL

    declare @table table (columnName varchar(64))

insert into @table
values
('3304.063'),
('3304. 0625'), --notice the space here
('39.53'),
('39.2')

--this would return nothing
select *
from @table
where columnName like '%.[0-9][0-9][0-9][0-9]%'

    How to find out if this is the case?

    select *
from @table
where columnName like '% %'

    Or, anything but numbers and decimals:

    select *
from @table
where columnName like '%[^.0-9]%'