Question

0

216

Views

Strip word from mysql value

Looking to run a select query to filter products by size, however the column Product_Size has characters after the number, for example 'inches'. I have tried the below but it brings back no results.

SELECT * FROM `products` where Product_size BETWEEN '2 inches' AND '4.9 inches'

How do I strip the word 'inches' from the query?

over 4 years ago · Santiago Trujillo

2 answers

Answer question

0

You must convert the strings to numeric values.
For your case this conversion can be done implicitly by adding 0:

SELECT * 
FROM `products` 
WHERE 
  Product_size LIKE '%inches'
  AND
  Product_size + 0 BETWEEN '2 inches' + 0 AND '4.9 inches' + 0

See a simplified demo.

over 4 years ago · Santiago Trujillo Report

Answer question

Find remote jobs

Accepted Answer · 2022-05-11T22:03:18.432Z

You can use replace()

  SELECT * FROM products 
  where Category_List =1 and Category_List_2=7 and 
  Designed_For_Who LIKE '%%' and (RRP between '0' and '100000') and 
  Product_size BETWEEN cast(replace('2 inches',' inches','') as int) AND 
  cast(replace('4.9 inches',' inches','') as int) 
  ORDER BY Name ASC

0

216

Strip word from mysql value

2 answers

0

0

Find remote jobs

Andres GPT