I am trying to create a function that outputs non-english words in a string but i am getting the following error:

 PLS-00103: Encountered the symbol "|" when expecting one of the
         following:
         := . ( @ % ;
         The symbol ":= was inserted before "|" to continue.

The code that gives the error:

CREATE OR REPLACE Function 
-- ...
IS
s varchar2(38);
lang varchar2(100);

begin
    s := -- ...
    lang := -- ...
    lang || s; -- trying to concate here

end;

The concatenation operator seems not to be working. Can anyone point out any mistake I'm making.

CodePudding user response：

As OldProgrammer wrote in a comment:

lang || s

should be

lang := lang || s

CodePudding user response：

The errors are:

• RETURN VARCHAR2 and not RETURN string
• REGEXP_LIKE returns a boolean and not a string.
• lang || s; should be lang := lang || s;

Which would give you the code:

CREATE OR REPLACE Function findAssamese(
  string IN varchar2
) RETURN VARCHAR2
IS
  s varchar2(38);
  lang varchar2(100);
begin
  for i in 1..length(string) loop
    s := Substr(string,i,1);
    if REGEXP_LIKE (s, '[A-Z]') OR REGEXP_LIKE (s, '[a-z]') then
      lang := lang || s; -- trying to concate here
    end if;
  end loop;
  return lang;
end;
/

However, you could simply write:

CREATE OR REPLACE Function findAssamese(
  string IN varchar2
) RETURN VARCHAR2
IS
begin
  return REGEXP_REPLACE(string, '[^A-Z]', NULL, 1, 0, 'i');
end;
/

db<>fiddle here