SUBSTR CASE MySQL-CodePudding

I have the following table:

Fruits & Vegetables
3 Apples
2 Oranges
Cucumber

I need to extract a the first letter of the string using a substring and a case in mysql: i.e. I need to extract 3, 2 & C

where if 3 then "Fruit"
where if 2 then "Fruit"
where if C then "Vegetable"

So I have used the following:

Type varchar(80)
AS
(case
    when SUBSTR('Fruits & Vegetables',1) = '3' then "Fruit"
when SUBSTR('Fruits & Vegetables',1) = '2' then "Fruit"
when SUBSTR('Fruits & Vegetables',1) = 'C' then "Vegetable"
else NULL
end),

what I get is Null as a result can someone tell me what is wrong with the code?

CodePudding user response：

try this query

SUBSTR(fruits_and_vegetables, 1, 1)

SELECT 
(case when SUBSTR(fruits_and_vegetables, 1, 1) = '3' then "Fruit" 
when SUBSTR(fruits_and_vegetables,1,1) = '2' then "Fruit" 
when SUBSTR(fruits_and_vegetables,1,1) = 'C' then "Vegetable" 
else NULL END) as `Fruits & Vegetables` FROM `table_name`