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

Erich Kitzmueller · Accepted Answer · 2014-10-28T14:32:02.193

3

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;

edited Oct 28 '14 at 14:32

answered Oct 17 '14 at 08:17

Erich Kitzmueller

36,381
5
80
102

FYI i forget to add 'GY' :P if somebody want to use properly add the 'GY' to the second if! – SüniÚr Oct 28 '14 at 11:43
1

@Csanesz: Thanks for the info, I have edited my answer accordingly. – Erich Kitzmueller Oct 28 '14 at 14:32

Multisync · Answer 2 · 2014-10-17T08:13:33.047

Try to replace substr(upper(v_name), 1, 1) with:

regexp_substr(upper(v_name), '^[[:alpha:][.cs.][.xx.][.yy.]]', 1, 1, 'i')

where
[:alpha:] is a special function (any letter) - actually, I don't know, maybe this function alone can find collating sequences (depending on NLS)

[.cs.] is an example of a collating sequence; [.xx.], [.yy.] - other collating sequences possible in your NLS

So the regexp_substr above tries to find any letter OR 'cs' OR 'xx' OR 'yy' and etc. It starts searching from position = 1 and returns occurance = 1. Finally, the search is case insentitive ('i')

But first of all you may check whether this function:

regexp_substr(upper(v_name), '^[[:alpha:]]', 1, 1, 'i')

would find your collating sequences (I'd be glad to know this)

thanks I wanted to use regexp. but this alpha give me error at oracle. So I cant use it now. So i think i will you the function that ammmoQ wrote it works perfectly. — SüniÚr, Oct 17 '14 at 08:48

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

2 Answers2