Find records WHERE substring number is less than specified number in SQL-CodePudding

I have a database where numbers are added in the records as substring like "NYC: 120000000", "San Fransico: 800000", "Chicago: 10000000" etc in the population column.

How can I fetch Records where a number in the substring is less than or greater than a specific number?

SELECT * FROM `cities` WHERE `population` < 10000000

So I should get "San Fransico: 800000"

CodePudding user response：

If the data has to be in such a format you could use substring_index as suggested in comments

select * from cities 
where cast(substring_index(city_value,':',-1) as decimal) > 10000;

It looks like : is a standard delimiter in this field.

Substring_index returns a substring of a string before a specified number of delimiter occurs. In this case, the -1 parameter returns everything to the right of the : delimiter

substring_index Documentation

CodePudding user response：

You can use substring_index with delimiter space starting from the end with position -1.
To sort by numerical order so that 9 comes before 10 we have to cast to integer.
As you can see it takes a lot of manipulation to extract the 2 values from the column. It would definitely be simpler and faster to have 2 separate columns.

create table cities(
  city varchar(100)
  );
insert into cities values
('NYC: 120000000'), 
('San Fransico: 800000'), 
('Chicago: 10000000');

SELECT
  LEFT(
    city, 
    CHAR_LENGTH(city) 
    - LOCATE(
      ' ', 
      REVERSE(city))
    - 1
    )
  as city
,

SUBSTRING_INDEX(city,' ',-1) population
from cities
order by
cast(
  substring_index(city,' ',-1)
  as unsigned)

city         | population
:----------- | :---------
San Fransico | 800000    
Chicago      | 10000000  
NYC          | 120000000

db<>fiddle here

CodePudding user response：

If you are using MySQL 8 you can use REGEXP_REPLACE:

select population, REGEXP_REPLACE(population, '[^0-9] ', '')  
from cities
where  REGEXP_REPLACE(population, '[^0-9] ', '') < 10000000;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0fa0a734260ec4b07c4f0bbe717e1068