I'm new to this.

I have a column: (chocolate_weight) On the table : (Chocolate) which has g at the end of every number, so 30x , 2x5g,10g etc.

I want to remove the letter at the end and then query it to show any that weigh greater than 35.

So far I have done

Select *
From Chocolate
Where chocolate_weight IN 
(SELECT
REPLACE(chocolote_weight,'x','') From Chocolate) > 35

It is coming back with 0 , even though there are many that weigh more than 35.

Any help is appreciated

Thanks

CodePudding user response：

If 'g' is always the suffix then your current query is along the right lines, but you don't need the IN you can do the replace in the where clause:

SELECT *
FROM Chocolate
WHERE CAST(REPLACE(chocolate_weight,'g','') AS DECIMAL(10, 2)) > 35;

N.B. This works in both the tagged DBMS SQL-Server and MySQL

This will fail (although only silently in MySQL) if you have anything that contains units other than grams though, so what I would strongly suggest is that you fix your design if it is not too late, store the weight as an numeric type and lose the 'g' completely if you only ever store in grams. If you use multiple different units then you may wish to standardise this so all are as grams, or alternatively store the two things in separate columns, one as a decimal/int for the numeric value and a separate column for the weight, e.g.

The issue you will have here though is that you will have start doing conversions in your queries to ensure you get all results. It is easier to do the conversion once when the data is saved and use a standard measure for all records.