CODEWITHSUNDEEP
sqlsqlitebetween
John Peabody
John Peabody

Reputation: 39

Select rows from SQL table within file size range

I have a column in my table that represents file sizes in human-friendly format. For example, 25MB, 300B, 76.5KB etc.. How would I write a query that would select rows that were within a size range? For example between 150kb and 6mb or betwe 35mb and 1.2GB?

I've learned how to order by file size which is a similar issue, but I haven't been able to use that for these purposes.

I can get a range of files that are sort of in my range, but files like 9mb will be ordered higher than 150mb files.

Upvotes: 0

Views: 933

Answers (2)

forpas
forpas

Reputation: 164099

You can do by storing with Row Values inside a CTE the values in Bytes of each one of B, KB, MB, GB and TB and use them when you want to perform search:

with cte(suffix, bytes) as (
  select * from (
    values 
      ('B', 1), ('KB', 1024), ('MB', 1024 * 1024), 
      ('GB', 1024 * 1024 * 1024), ('TB', 1024 * 1024 * 1024 * 1024)
  )
)
select f.* from filtered f 
where 
  f.size * (select max(bytes) from cte where f.size like '%' || suffix) 
  between 
  '150MB' * (select max(bytes) from cte where '150MB' like '%' || suffix) 
  and 
  '6GB' * (select max(bytes) from cte where '6GB' like '%' || suffix)

Replace '150MB' and '6GB' with your search values.
See the demo.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can convert the value to an actual number. One method would be to explicitly translate each suffix:

select (case when size like '%KB' then 1000
             when size like '%MB' then 1000000
             when size like '%GB' then 1000000000
             else 1
        end) * (size + 0.0)
from table_name 
where size between 750 and 1000

Two notes. Suffixes such as "MB" are ambiguous. It can mean either 1,000,000 or 2^20 (1,048,576). That means that the factors might be different.

The size + 0.0 uses SQLite's capability to do implicit conversion, based on leading numeric digits.

Upvotes: 2

Related Questions