use select case in Mysql

Question

The question I try to solve:

Return the value for "prefix" following these conditions:

• If model_hyouji is a number and ranging from 0 to 9, set prefix = 0
• If model_hyouji is a letter and ranging from a to z or A to Z, set prefix = 1
• In all other cases, set prefix = 2

I used this SQL:

SELECT model_code,
       model_hyouji,
       model_name_prefix,
       model_kana_prefix,
       model_count,
       case prefix
            when 'a' then 1
            when 'b' then 2
            else 3
       end
FROM    webikept.mst_model_v2 
where   model_maker_code = 1 
and     model_displacement between 51 and 125 
and     (SELECT substring(model_hyouji,1,1) as prefix
        FROM webikept.mst_model_v2 )

and I get this error:

Unknown column 'prefix' in 'field list'

Answer 1

assuming you column model_hyouji is varchar(1) (otherwise use substr(model_hyouji,1) )

  SELECT model_code,
         model_hyouji,
         model_name_prefix,
         model_kana_prefix,
         model_count,
         case 
              when  model_hyouji between '0' and '9' then 0 
              when  model_hyouji between 'a' and 'z' then 1 
              when  model_hyouji between 'A' and 'Z' then 1 
              else 2 
          end prefix
  FROM    webikept.mst_model_v2 
  where   model_maker_code = 1 
  and     model_displacement between 51 and 125 
  and     (SELECT substring(model_hyouji,1,1) as prefix
          FROM webikept.mst_model_v2 )

or you can use regexp

  SELECT model_code,
         model_hyouji,
         model_name_prefix,
         model_kana_prefix,
         model_count,
         case 
              when  model_hyouji REGEXP  '^[0-9]{1}$' then 0 
              when  model_hyouji  REGEXP  '^[A-Za-z]$' then 1 
              else 2 
          end prefix
  FROM    webikept.mst_model_v2 
  where   model_maker_code = 1 
  and     model_displacement between 51 and 125 
  and     (SELECT substring(model_hyouji,1,1) as prefix
          FROM webikept.mst_model_v2 )

Answer 2

    SELECT 

  model_code,model_hyouji,model_name_prefix,model_kana_prefix,model_count,
  case 
  when prefix = 'a' then 1 when 'b' then 2 else 3 end

You need CASE WHEN prefix =

But you also dont specify which prefix column you want to use hence the error on unknown column

E.G

  SELECT 

  model_code,model_hyouji,model_name_prefix,model_kana_prefix,model_count,
  case 
  WHEN model_kana_prefix = 'a' then 1 
  WHEN model_kana_prefix = 'b' then 2 
  else 3 end