basic instring question

[eyetry]

I'm (putting it mildly) a novice,struggling, here so any help is great..

In a PLSQL job I was handed there is a string that will be variable. I need to check the string to see if some specific values are present.... To be mose specific I need to check the path of an input file to determine customer name and execute sub procedures accordingly. The path may look like...

\\path\...\customer1\...\file.

In this case I will have up to 5 possible customer names and position of the customer name may vary based on business type, server etc...

The path has been declaired as l_input, and l_customer_name is declared for the result.

How do I determine if l_path contains x verses y?

 

[eyetry]

sorry should be...

How do I determine if l_input contains x verses y?

 

[Thargy]

Eyetry,

oracle has the instr() function, which will do what you want. You should be aware that there are five variations on INSTR, and you should make it your business to look them up in the oracle documentation. However, we'll stick to the most likely one for now, which is plain vanilla INSTR.

Instr has usage of INSTR(string_to_search, search_string), i.e. it looks through the first string for the second, and if found, returns the position of the second. If no match is found, INSTR returns a zero.
Run the following from sql plus (or whatever environment you have)

SELECT INSTR('eyetry', 'etr') from dual;

and you will get the answer of 3.

If you run

SELECT INSTR('eyetry', 'zzz') from dual;

you will get an answer of zero, i.e. no match found.
Note also that INSTR is case sensitive. If you search 'eyetry' for 'EYE' the result is zero.

Does this meets your requirements?

Regards

Tharg


Grinding away at things Oracular

 

[eyetry]

Not sure this is the right way to do this typ of thing but this is what I did....

declare
l_path varchar2(255):= '\\path\CUSTOMERn\path_continued\f_name;
l_provider varchar2(255);
l_provider_check number(03);

begin

select instr(upper(l_path ),'CUSTOMER1',1) into l_provider_check from dual;
if l_provider_check > 0 then
l_provider := 'provider1';
else
select instr(upper(l_path ),'CUSTOMER2',1) into l_provider_check from dual;
if l_provider_check > 0 then
l_provider := 'provider2';
dbms_output.put_line(l_provider);
else
select instr(upper(l_path ),'CUSTOMER3',1) into l_provider_check from dual;
if l_provider_check > 0 then
l_provider := 'provider3';
dbms_output.put_line(l_provider);
else
select instr(upper(l_path ),'CUSTOMER4',1) into l_provider_check from dual;
if l_provider_check > 0 then
l_provider := 'provider4';
dbms_output.put_line(l_provider);
end if;
end if;
end if;
end if;
.
.
.

Seems to work but feels kind of klunky...

Thanks, considering this complete (unless someone sees a better way)

 

[Thargy]

Eyetry,

excellent work for deploying the instr function!

You are obviously more experienced than you led me to believe, as your instincts are sound. Your code is indeed 'clunky'. Look in oracle's online documentation for the case statement. It's designed precisely for the situation above, where hordes of if-then-else statements would otherwise be required.

Would you like to attempt the use of a CASE statement yourself, or do you want some more help with it?

Regards

Tharg

Grinding away at things Oracular

 

[eyetry]

Well thanks, appreciate the comments! Think I'll look into the CASE function see what I can determine. 1st though I need to work on the encryption & data transfer part of my script. Think that'll go fast though. I just need to find an example in our scheduler and figure out how to mirror it.

Thanks again, Eyetry

 

[BJCooperIT]

Just a quick comment. You really don't need to slow things down by selecting from dual. Try:

l_provider_check := instr(upper(l_path ),'CUSTOMER4',1);

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[gunjan14]

Hi,
If you are using Oracle 9i or above then you can use owa_pattern also. Although it is documented in 10g but still works in 9i.
Here is the code

SQL> create or replace function test_string
  2  (
  3      strtochk  in varchar2,
  4      expr      in varchar2
  5  )
  6  return number
  7  as
  8  begin
  9      if owa_pattern.match(strtochk, expr)
 10      then
 11          return 1;
 12      else
 13          return 0;
 14      end if;
 15  end;
 16  /

Function created.

SQL> select test_string('\\path\CUSTOMER6\path_continued\f_name', 'CUSTOMER\d') match from dual;

     MATCH
----------
         1

SQL> select test_string('\\path\CUSTOMER6\path_continued\f_name', 'CUST.*\d') match from dual;

     MATCH
----------
         1

SQL> select test_string('\\path\CUSTOMER6\path_continued\f_name', 'GUNJ.*') match from dual;

     MATCH
----------
         0

SQL> set serveroutput on
SQL> declare
  2      l_path varchar2(50);
  3      l_numb number;
  4      l_bool boolean;
  5  begin
  6      l_path := '\\path\CUSTOMER6\path_continued\f_name';
  7      -- created function so that can use on select statement
  8      l_numb := test_string(l_path, 'CUSTOMER\d');
  9      
 10      -- if only need to use in PL/SQL then no need to create function
 11      l_bool := owa_pattern.match(l_path, 'CUSTOMER\d');
 12  
 13      if l_numb = 1
 14      then
 15          dbms_output.put_line('Pattern Matches with function : CUSTOMER\d');
 16      else
 17          dbms_output.put_line('Pattern do not match          : CUSTOMER\d');
 18      end if;
 19  
 20      if l_bool 
 21      then
 22          dbms_output.put_line('Pattern Matches with function : CUSTOMER\d');
 23      else
 24          dbms_output.put_line('Pattern do not match          : CUSTOMER\d');
 25      end if;
 26      
 27      l_numb := test_string(l_path, 'GUNJ\d');
 28      if l_numb = 1
 29      then
 30          dbms_output.put_line('Pattern Matches with function : GUNJ\d');
 31      else
 32          dbms_output.put_line('Pattern do not match          : GUNJ\d');
 33      end if;
 34      
 35      l_numb := test_string(l_path, 'CUST.*\d');
 36      if l_numb = 1
 37      then
 38          dbms_output.put_line('Pattern Matches with function : CUST.*\d');
 39      else
 40          dbms_output.put_line('Pattern do not match          : CUST.*\d');
 41      end if;
 42  end;
 43  /
Pattern Matches with function : CUSTOMER\d
Pattern Matches with function : CUSTOMER\d
Pattern do not match          : GUNJ\d
Pattern Matches with function : CUST.*\d

PL/SQL procedure successfully completed.

The first is path to check and second is a regular expression stating what to check. This way you do not need to write so many statements.

Regards,
Gunjan