CODEWITHSUNDEEP
oracle-databaseplsqlsubstring
C Durai
C Durai

Reputation: 25

How to search for a string in pl/sql

I need to search whether a mail id is available in the group of IDs seperated by comma.

I am trying with the following code.

It shows 'available' for unavailable IDs also.

CREATE OR REPLACE PROCEDURE sample 
AS
  email_id VARCHAR2(1000);
  mail     VARCHAR2(100);    
  BEGIN
    email_id := '[email protected], [email protected], [email protected], [email protected], [email protected]';
    mail := '[email protected]';

    IF (INSTR(email_id, mail) IS NOT NULL) THEN
      dbms_output.PUT_LINE('available');
    ELSE
      dbms_output.PUT_LINE('sorry');
    END IF;

  END sample;

It is not working properly. Please help me.

Upvotes: 1

Views: 6209

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

INSTR() would return 0, if it didn't find a matching substring in the source(Unless the input itself is NULL). So when you check for NOT NULL, it always returns TRUE.

If it finds a matching substring, it would return the position of the substring in the source.(NUMBER)

So for your case, LIKE or REGEXP_LIKE should help.
Still you can go for the below.

if(INSTR (EMAIL_ID, MAIL) > 0)
then
  DBMS_OUTPUT.PUT_LINE('available');
else 
  DBMS_OUTPUT.PUT_LINE('sorry');
end if;

Documentation From Oracle.

Upvotes: 3

Avrajit
Avrajit

Reputation: 230

Hi i guess instead of declaring a full new variable and then using just use LIKE 
operator. I think it should resolve your query.
DECLARE
  lv_var VARCHAR2(100);
BEGIN
  lv_var:='[email protected],[email protected]';
  IF lv_var LIKE '%avra%' THEN
    dbms_output.put_line('Available');
  ELSE
    dbms_output.put_line('Sorry');
  END IF;
END;

OUTPUT 
------------------------------------------------
anonymous block completed
Available
------------------------------------------------

Upvotes: 1

Related Questions