I`m trying to get a specific result. The conditions are simple but still... is query ok?

• If first letter of source is 'M' or 'm', load 'M' in target
• If first letter of source is 'F' or 'f', load as 'F' in target
• Else set to NULL

case when substr(GENDER,1,1) = 'M' then 'M',
when substr(GENDER,1,1) = 'm' then 'M'
when substr(GENDER,1,1) = 'F' then 'F'
when substr(GENDER,1,1) = 'f' then 'F'
else NULL

CodePudding user response：

Somewhat simpler

case when upper(substr(gender, 1, 1)) in ('M', 'F') then upper(substr(gender, 1, 1))
     else null
end

but ... yes, your code would do as well. Didn't you test it?

CodePudding user response：

You have a stray comma and no end to the case expression, but apart from that what you have works.

You could simplify it to a simple (rather than searched) case expression:

case substr(GENDER,1,1)
  when 'M' then 'M'
  when 'm' then 'M'
  when 'F' then 'F'
  when 'f' then 'F'
  else NULL
end as target

or even shorter:

case upper(substr(GENDER,1,1))
  when 'M' then 'M'
  when 'F' then 'F'
  else NULL
end as target

or as the else NULL is implied you could leave that out too:

case upper(substr(GENDER,1,1))
  when 'M' then 'M'
  when 'F' then 'F'
end as target

db<>fiddle

CodePudding user response：

If you just want an uppercase letter with the whole word, do something like this:

SELECT upper(substr(GENDER,1,1))||substr(GENDER,2,length(GENDER)-1) as target

If you just need the short form like 'F' or 'M', you just need the first part of the statement mentioned before:

SELECT upper(substr(GENDER,1,1) as target