Home » SQL & PL/SQL » SQL & PL/SQL » searching for a string in a string
searching for a string in a string [message #226348] Fri, 23 March 2007 05:48 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a string, and i want to know whether
"hello" is a part of that string or not?

i have seen all the built in functions, but not sure which
one to use...

can anyone please help me, i want to know whethere "hello"
is part of a given string or not..

which built in functions to use?
Re: searching for a string in a string [message #226353 is a reply to message #226348] Fri, 23 March 2007 06:04 Go to previous messageGo to next message
meggens
Messages: 57
Registered: February 2007
Location: Netherlands
Member
Hi,

maybe this will help you:

SELECT INSTR('just testing hello, how are you?', 'hello', 1, 1)
FROM dual;

it will return the position where 'hello' starts. (14)
if it returns 0 hello is not is the string.

regards,
martin
Re: searching for a string in a string [message #226359 is a reply to message #226348] Fri, 23 March 2007 06:21 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
actually for any string, i should first find out, whethere
"hello" is there, if not, i should concatenate that string
with hello..

i should use decode,

i am struck here

declare
l_value varchar2(40):='xyz ';
begin
select decode(INSTR(l_value, 'hello', 1, 1),

end;
Re: searching for a string in a string [message #226362 is a reply to message #226348] Fri, 23 March 2007 06:31 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
got it, thanks

declare
l_value varchar2(40):='hh';
l_print varchar2(40);
begin
select decode(INSTR(l_value, ' hello', 1, 1),0,l_value||'hello',l_value) into l_print from dual;
dbms_output.put_line(l_print);


end;
Re: searching for a string in a string [message #226445 is a reply to message #226362] Fri, 23 March 2007 15:05 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Use this instead, keep it simple.

declare
l_value varchar2(40):='hh';
begin
    dbms_output.put_line(decode(INSTR(l_value, ' hello', 1, 1),0,l_value||'hello',l_value));
end;
Re: searching for a string in a string [message #226446 is a reply to message #226445] Fri, 23 March 2007 15:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2  l_value varchar2(40):='hh';
  3  begin
  4      dbms_output.put_line(decode(INSTR(l_value, ' hello', 1, 1),0,l_value||'hello',l_value));
  5  end;
  6  /
    dbms_output.put_line(decode(INSTR(l_value, ' hello', 1, 1),0,l_value||'hello',l_value));
                         *
ERROR at line 4:
ORA-06550: line 4, column 26:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

simple but still correct. Neutral

Regards
Michel

[Updated on: Fri, 23 March 2007 15:11]

Report message to a moderator

Re: searching for a string in a string [message #226448 is a reply to message #226348] Fri, 23 March 2007 15:09 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Sorry, I jumped to fast. I should (and do) know better.
Re: searching for a string in a string [message #226461 is a reply to message #226359] Fri, 23 March 2007 17:58 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
DECLARE
    v_value VARCHAR2(40) := 'xyz';
BEGIN
    DBMS_OUTPUT.PUT_LINE
    ( 'String ' ||
      CASE WHEN v_value LIKE '%hello%' THEN 'contains' ELSE 'does not contain' END  ||
      ' the text ''hello''.' );
END;


Corrected - thanks Michel.

[Updated on: Sat, 24 March 2007 04:11]

Report message to a moderator

Re: searching for a string in a string [message #226485 is a reply to message #226461] Sat, 24 March 2007 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you meant "LIKE '%hello%'".
instr should be faster:
SQL> DECLARE
  2      v_value VARCHAR2(40) := 'xyz';
  3  BEGIN
  4      DBMS_OUTPUT.PUT_LINE
  5      ( 'String ' ||
  6        CASE WHEN instr(v_value, 'hello') > 0 THEN 'contains' ELSE 'does not contain' END  ||
  7        ' the text ''hello''.' );
  8  END;
  9  /
String does not contain the text 'hello'.

Regards
Michel
Re: searching for a string in a string [message #226499 is a reply to message #226485] Sat, 24 March 2007 04:42 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Oops, thanks for the correction.

I expected LIKE and INSTR to be similar in performance, but I prefer LIKE as it seems to me to state more clearly what the code is supposed to do. Maybe in this case it's pretty clear anyway.

I tried a performance test but I got rather inconsistent results - this is on my home desktop system so most likely the various background processes competed for resources, even when I closed down Thunderbird and iTunes etc. Anyway I had to have it loop 10 million times to get results above one second so I wouldn't say there was much in it.

SQL> ed
Wrote file /Users/williamr/SQL/afiedt.buf

  1  DECLARE
  2      v_value VARCHAR2(40) := 'xyz';
  3      v_start TIMESTAMP := SYSTIMESTAMP;
  4      v_elapsed INTERVAL DAY(1) TO SECOND(2);
  5      k_iterations CONSTANT PLS_INTEGER := 1e7;
  6      v_test_result VARCHAR2(100);
  7  BEGIN
  8      v_start := SYSTIMESTAMP;
  9      --
 10      FOR i IN 1..k_iterations LOOP
 11          v_test_result :=
 12          'String ' ||
 13          CASE WHEN v_value LIKE '%hello%' THEN 'contains' ELSE 'does not contain' END  ||
 14          ' the text ''hello''.';
 15      END LOOP;
 16      --
 17      v_elapsed := SYSTIMESTAMP - v_start;
 18      DBMS_OUTPUT.PUT_LINE('LIKE:  ' || v_elapsed);
 19      v_start := SYSTIMESTAMP;
 20      --
 21      FOR i IN 1..k_iterations LOOP
 22          v_test_result :=
 23          'String ' ||
 24          CASE WHEN instr(v_value, 'hello') > 0 THEN 'contains' ELSE 'does not contain' END  ||
 25          ' the text ''hello''.';
 26      END LOOP;
 27      --
 28      v_elapsed := SYSTIMESTAMP - v_start;
 29      DBMS_OUTPUT.PUT_LINE('INSTR: ' || v_elapsed);
 30* END;
SQL> /
LIKE:  +0 00:00:03.92
INSTR: +0 00:00:02.43

PL/SQL procedure successfully completed.

SQL> /
LIKE:  +0 00:00:06.62
INSTR: +0 00:00:07.33

PL/SQL procedure successfully completed.

SQL> /
LIKE:  +0 00:00:02.36
INSTR: +0 00:00:02.59

PL/SQL procedure successfully completed.

SQL> /
LIKE:  +0 00:00:03.76
INSTR: +0 00:00:03.32

PL/SQL procedure successfully completed.

SQL> 

[Updated on: Sat, 24 March 2007 04:45]

Report message to a moderator

Previous Topic: cannot drop table
Next Topic: Grouping
Goto Forum:
  


Current Time: Sat Dec 03 13:56:12 CST 2016

Total time taken to generate the page: 0.08054 seconds