Skip to content
Advertisement

How to return rows containing decimals in a range

I have the following table:

Rows Decimals
First 1.1.1.3.2
Second 16.1.1.1.89.1
Third 3.1.1.1.177.2
Fourth 1.1.1.1.178.3

I only want to return the rows where the second to last decimal is between 0.7 to 0.94 (inclusive), but all the other numbers can contain any value. For the above table, this would be only the second row. How can I specify my query to specifically look at the second to last decimal place?

Here is the code I have so far:

SELECT Rows FROM table
WHERE Decimals like %%.%%.%%.7.%% < %%.%%.%%.94.%%;

Advertisement

Answer

select `rows`,
       `decimals`
  from your_table
 where substring_index(reverse(substring_index(reverse(`decimals`),'.',2)),'.',1) between 7 and 94;

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement