SQL - Like and greater than Function

user_5893585 0
Viewed 197

I have a dataset like below:

table_a

Product_Name      Product_Orders
    game_296                   1
    game_298                   2
    game_299                   4
    300_game                   6
    xyz_game                   9
    game-tyw                  12

How do I do use a like function in SQL and combine with a greater than? My overall goal is to filter games that are greater than a certain number like 297.

I would ideally like to do something like this:

select * from table_a
where Product_Name > ilike %297%

The expected output here would be this:

Product_Name      Product_Orders
    game_298                   2
    game_299                   4
    300_game                   6
snowflake-cloud-data-platform sql-like sql database
3 Answers

One way would be to remove all non-digits from the string and then do the comparison:

where cast(regexp_replace(product_name, '[^0-9]', '') as int) > 297
user_11440350

Try this:

-- your input ....
WITH
indata(Product_Name,Product_Orders) AS (
          SELECT 'game_296', 1
UNION ALL SELECT 'game_298', 2
UNION ALL SELECT 'game_299', 4
UNION ALL SELECT '300_game', 6
UNION ALL SELECT 'xyz_game', 9
UNION ALL SELECT 'game-tyw',12
)
-- real query starts here ...
SELECT
  product_name
, product_orders
FROM indata
WHERE CAST(REGEXP_SUBSTR(product_name,'\d+') AS INTEGER) > 297;
-- out  product_name | product_orders 
-- out --------------+----------------
-- out  game_298     |              2
-- out  game_299     |              4
-- out  300_game     |              6
-- out (3 rows)
user_67882550

Maybe like this:

select * from table_a
where Product_Name like %[2][9][8-9]% or Product_Name like %[3-9][0-9][0-9]%

I don't know the SQL you use, it may be wrong as a syntax.

user_145723520
Related