Oracle PL/SQL get the first letter from the name(HUN)

Question

The question sounds easy because only need a substring (name,1,1). But in the Hungarian language there are many letter wich contains multicharacter. eg : CS,DZ,DZS,LY,NY,SZ,TY,ZS

  IF v_type='by_name' THEN  
    select DISTINCT name
      into v_result
    from my_table
    where instr(_START_LETTERS_,substr(upper(v_name),1,1))>0 and ZIPCODE = v_act_zipcode;
    return v_result;

and my table eg:

zipcode name  _START_LETTERS
1234   Ryan    A,B,C
1234   Bryan   CS,D

And if i want to get my name CSanád i need to get CS not the first char C-> becuase i will get multirow exception.

Do you have anysuggestion to use get the first lettor? Or I have to write a huge if-else/case structure to make my code awful and impenetrable.

Thanks

Answer 1

I think the most straight-forward solution is to write a stored function that extracts the first letter:

create function hun_first_letter(name in varchar2) return varchar2 as
begin
  if substr(upper(name),1,3) in ('DZS') then
    return substr(name,1,3);
  elsif substr(upper(name),1,2) in ('CS','DZ','LY','NY','SZ','TY','ZS','GY') then
    return substr(name,1,2);
  else
    return substr(name,1,1);
  end if;
end;