Home » SQL & PL/SQL » SQL & PL/SQL » Searching Text Query - Using SQL
Searching Text Query - Using SQL [message #216254] Thu, 25 January 2007 14:41 Go to next message
Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom
Junior Member
Hi can anyone help?

I have data like this

id  value
1   'blablaba 123 blabla 234'
2   'blabla 234 blablabla 345'


I want to search the value column with 2 parameters, with values like for example 123 and 234, and I need the output to be:
id  search_hit  value
1   123         'blablaba 123 blabla 234'
1   234         'blablaba 123 blabla 234'
2   234         'blabla 234 blablabla 345'


Is there anyway doing this via SQL?

Thanks
Jon
Re: Searching Text Query - Using SQL [message #216270 is a reply to message #216254] Thu, 25 January 2007 22:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
There are always various ways to do things. My inclination would be to use Oracle Text, like so:


SCOTT@10gXE> CREATE TABLE your_table
  2    (id	   NUMBER,
  3  	the_value  VARCHAR2 (60))
  4  /

Table created.

SCOTT@10gXE> INSERT ALL
  2  INTO your_table VALUES (1, 'blablaba 123 blabla 234')
  3  INTO your_table VALUES (2, 'blabla 234 blablabla 345')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@10gXE> CREATE INDEX your_index ON your_table (the_value)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@10gXE> VARIABLE param1 VARCHAR2(15)
SCOTT@10gXE> VARIABLE param2 VARCHAR2(15)
SCOTT@10gXE> EXEC :param1 := '123'

PL/SQL procedure successfully completed.

SCOTT@10gXE> EXEC :param2 := '234'

PL/SQL procedure successfully completed.

SCOTT@10gXE> COLUMN search_hit FORMAT A15
SCOTT@10gXE> SELECT t.id, p.search_hit, t.the_value
  2  FROM   your_table t,
  3  	    (SELECT :param1 AS search_hit FROM DUAL
  4  	     UNION ALL
  5  	     SELECT :param2 AS search_hit FROM DUAL) p
  6  WHERE  CONTAINS (t.the_value, p.search_hit) > 0
  7  /

        ID SEARCH_HIT      THE_VALUE
---------- --------------- ------------------------------------------------------------
         1 123             blablaba 123 blabla 234
         1 234             blablaba 123 blabla 234
         2 234             blabla 234 blablabla 345

SCOTT@10gXE> 



Re: Searching Text Query - Using SQL [message #216291 is a reply to message #216270] Fri, 26 January 2007 03:04 Go to previous messageGo to next message
Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom
Junior Member
Hi Barbara

Thanks but I think you may have been misunderstood.
My SQL is as below

SELECT bus_region.orderobjectidentifier
      ,bus_region.ordersource
      ,bus_region.ordertext1
  FROM stage.bus_region bus_region
 WHERE bus_region.ordertext1 LIKE '%234%' OR
       bus_region.ordertext1 LIKE '%345%'


So within bus_region.ordertext1 tables is the data I would like to search
id  value
1   'blablaba 123 blabla 234'
2   'blabla 234 blablabla 345'


I won't know what is actually contained when I do my search. Which is when I do it and get the result I would like to have returned

id  search_hit  value
1   123         'blablaba 123 blabla 234'
1   234         'blablaba 123 blabla 234'
2   234         'blabla 234 blablabla 345'


I should also add that I do have limited access to the internals of the database itself in case that makes a difference.

Many thanks
Jon



Re: Searching Text Query - Using SQL [message #216293 is a reply to message #216291] Fri, 26 January 2007 03:35 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member

with the help of substr & instr function u get ur desired result.
try to think ..........
hope this clue helps u

bye
Re: Searching Text Query - Using SQL [message #216294 is a reply to message #216291] Fri, 26 January 2007 03:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The solution that I provided is a perfectly valid solution for the problem that you originally presented. The sql statement that you now provided bears no relation to your desired results. All you need to do is substitute the table and column names in my example and what I posted is still a valid solution. If you want to use like instead of an Oracle Text index and hardcode your so-called parameters, instead of using bind variables, then, modifying your new query, you could do something like:

SELECT bus_region.orderobjectidentifier
      ,bus_region.ordersource
      ,'234' AS search_hit   
      ,bus_region.ordertext1
  FROM stage.bus_region bus_region,
 WHERE bus_region.ordertext1 LIKE '%234%' 
UNION ALL
SELECT bus_region.orderobjectidentifier
      ,bus_region.ordersource
      ,'345' AS search_hit
      ,bus_region.ordertext1
  FROM stage.bus_region bus_region
 WHERE bus_region.ordertext1 LIKE '%345%';


Please read the sticky at the top of the forum for how to post a proper question with create table statements, insert statements for sample data, and the results that you want based on that data.






Re: Searching Text Query - Using SQL [message #216398 is a reply to message #216294] Sat, 27 January 2007 03:02 Go to previous messageGo to next message
Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom
Junior Member
Thanks guys

I sorry but I am a really novice so you'll have to excuse me.

Barbara, I tried your suggestion but this failed but the error message wasn't helpful as didn't tell me why it failed.

ashish_pass1, I am a novice & don't really understand how substr & instr work.. Sorry.

Thanks
Jon

Re: Searching Text Query - Using SQL [message #216399 is a reply to message #216398] Sat, 27 January 2007 03:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Substr and instr would not help with this problem. Please post a copy and paste of an actual run of what you tried, complete with the error message. The error message might not mean anything to you, but it might to some of the rest of us.
Re: Searching Text Query - Using SQL [message #216410 is a reply to message #216399] Sat, 27 January 2007 05:42 Go to previous messageGo to next message
Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom
Junior Member
All I got was ERROR and nothing else.

I'll take another look when I get to work on Monday.

Thanks
Jon
Re: Searching Text Query - Using SQL [message #216556 is a reply to message #216410] Mon, 29 January 2007 06:43 Go to previous messageGo to next message
srhl
Messages: 24
Registered: December 2005
Junior Member
select id, the_value ,
case
when (instr(the_value,'123')>0) then substr(the_value,instr(the_value,'123'),3)
end,
case
when (instr(the_value,'234')>0) then substr(the_value,instr(the_value,'234'),3)
end
from your_table

Hope this would help!!!!
Re: Searching Text Query - Using SQL [message #216558 is a reply to message #216556] Mon, 29 January 2007 07:13 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I think INSTR can help. I just replaced the CONTAINS in Barbara's query:

SELECT t.id, p.search_hit, t.the_value
    FROM   your_table t,
    	    (SELECT 123 AS search_hit FROM DUAL
    	     UNION ALL
    	     SELECT 234 AS search_hit FROM DUAL) p
    WHERE  INSTR (t.the_value, p.search_hit) > 0
Re: Searching Text Query - Using SQL [message #216590 is a reply to message #216556] Mon, 29 January 2007 10:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
srhl wrote on Mon, 29 January 2007 04:43
select id, the_value ,
case
when (instr(the_value,'123')>0) then substr(the_value,instr(the_value,'123'),3)
end,
case
when (instr(the_value,'234')>0) then substr(the_value,instr(the_value,'234'),3)
end
from your_table

Hope this would help!!!!


That would provide two hit columns, instead of one, as originally specified, and with some unnecessary code. The then clauses could be simplified to just "then '123'" and "then '234'".


Re: Searching Text Query - Using SQL [message #216592 is a reply to message #216558] Mon, 29 January 2007 10:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
a_developer wrote on Mon, 29 January 2007 05:13
I think INSTR can help. I just replaced the CONTAINS in Barbara's query:

SELECT t.id, p.search_hit, t.the_value
    FROM   your_table t,
    	    (SELECT 123 AS search_hit FROM DUAL
    	     UNION ALL
    	     SELECT 234 AS search_hit FROM DUAL) p
    WHERE  INSTR (t.the_value, p.search_hit) > 0



There are just a few problems with that, but they can be corrected. If, for example, you want only the individual value 123 and not another value such as 41235 that contains 123, then you need to search for ' 123 ', but then you have a problem when the values are the first or last in the string without a leading or trailing space, so you need to concatenate a space before and after the t.the_value as well, but then your search is not likely to be using an index. Therefore, the Oracle Text method which is designed for just such purposes is likely to be the most efficient.

Re: Searching Text Query - Using SQL [message #217244 is a reply to message #216592] Thu, 01 February 2007 05:33 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Yes, you're right.Thanks for the knowledge!
Previous Topic: problem with loading the ids into table
Next Topic: problem with plsql tables
Goto Forum:
  


Current Time: Sun Dec 11 06:25:29 CST 2016

Total time taken to generate the page: 0.09519 seconds