0

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?

Basel Ahmed
  • 19
  • 4
  • if Fruits & Vegetables is a column name then it should be enclosed in backticks https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql – P.Salmon Nov 30 '22 at 09:30
  • @P.Salmon, I believe that's what's already present 'Fruits & Vegetables' – Basel Ahmed Nov 30 '22 at 09:36
  • Hmm looks like single quotes to me and substr needs 3 arguments eg SUBSTR(`Fruits & Vegetables`,1,1) = '3' https://dbfiddle.uk/oQGOWblG – P.Salmon Nov 30 '22 at 09:38

1 Answers1

0

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`
Bhargav Chudasama
  • 6,928
  • 5
  • 21
  • 39