Home » SQL & PL/SQL » SQL & PL/SQL » Parsing and searching for each row (10G)
Parsing and searching for each row [message #338900] Wed, 06 August 2008 04:07 Go to next message
TJPokala
Messages: 17
Registered: March 2008
Junior Member
I have two set of txt files.

1) Links - 1 million records
2) Projects - 500 records

I need to know how to search in the link if a project is present in that link and map it back to the projects.

For e.g

LINKS
----------
11X3 O2W HUA00 ETH-1.5M001
11X8 O2U D1000 MUL0001
11X8 O2U D1000 MUL0002

PROJECTS
-----------------

11X3
11X8
02W
02U


So as you can Link1 has two sites which is divided into two columns SiteA =11X3 SiteB =02W

And goes on....

There are some rubbish data which is no way there is any site in this
0111111111111111V@WTF.IHATEME xxx001
6611111111111111V@WTF.YOUHATEME xxx001
5011111111111111T@WTF.WHYHATEME xxx001
0311111111111111T@WTF.ILOVEMExxx001
which can be ignored.


Is there a parsing mechanism to go through row by row in oracle.How to start on this?

TABLE_LINKS
LINKCODE VARCHAR2(50 BYTE)
--1 million records

TABLE_PROJECTS
PROJECT CODECODE
--500 records

In every record/row in table_link will be associated with a site_code in table_projects.

I need to create 2 columns in TABLE_LINKs as SiteA,SiteB.(e.g 11X3 ,O2W )
as the nature of data has two site code or maybe one.

Some are rubbish links which cannot track down the site_code.

Teh position of teh siteA,SiteB in the table_link can be anywhere.

How do we do a search in the 1 million records whether each row contains any of teh 500 records in Table_projects.

[Updated on: Wed, 06 August 2008 04:11]

Report message to a moderator

Re: Parsing and searching for each row [message #338905 is a reply to message #338900] Wed, 06 August 2008 04:17 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

http://www.orafaq.com/forum/t/88153/0/

Please read & follow Posting Guideline as stated in URL above
Re: Parsing and searching for each row [message #338913 is a reply to message #338900] Wed, 06 August 2008 04:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is there a fixed structure to the LINKS table?

How can you tell if a space delimited fragment from a row in LINKS is meant to be a value in PROJECT, or is meant to be something else?
Re: Parsing and searching for each row [message #339094 is a reply to message #338900] Wed, 06 August 2008 14:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You should be able to do this efficiently with an Oracle Text ctxrule index and matches as demonstrated below. You could also do it with just instr, but it would be slower.

-- test tables and data:
SCOTT@orcl_11g> CREATE TABLE links
  2    (link_code  VARCHAR2 (40))
  3  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO links VALUES ('11X3 O2W HUA00 ETH-1.5M001')
  3  INTO links VALUES ('11X8 O2U D1000 MUL0001')
  4  INTO links VALUES ('11X8 O2U D1000 MUL0002')
  5  INTO links VALUES ('0111111111111111V@WTF.IHATEME xxx001')
  6  INTO links VALUES ('6611111111111111V@WTF.YOUHATEME xxx001')
  7  INTO links VALUES ('5011111111111111T@WTF.WHYHATEME xxx001')
  8  INTO links VALUES ('0311111111111111T@WTF.ILOVEMExxx001')
  9  SELECT * FROM DUAL
 10  /

7 rows created.

SCOTT@orcl_11g> CREATE TABLE projects
  2    (project_code VARCHAR2 (30))
  3  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO projects VALUES ('11X3')
  3  INTO projects VALUES ('11X8')
  4  INTO projects VALUES ('O2W')
  5  INTO projects VALUES ('O2U')
  6  SELECT * FROM DUAL
  7  /

4 rows created.


-- add sitea and siteb columns to links table:
SCOTT@orcl_11g> ALTER TABLE links ADD (sitea VARCHAR2 (15), siteb VARCHAR2 (15))
  2  /

Table altered.


-- create Oracle Text ctxrule index on project_code column of projects table:
SCOTT@orcl_11g> CREATE INDEX link_code_idx ON projects (project_code)
  2  INDEXTYPE IS CTXSYS.CTXRULE
  3  /

Index created.


-- update sitea and siteb columns of links table:
SCOTT@orcl_11g> UPDATE links l
  2  SET    l.sitea =
  3  	    (SELECT MIN (p.project_code) KEEP
  4  		      (DENSE_RANK FIRST ORDER BY INSTR (l.link_code, p.project_code))
  5  	     FROM   projects p
  6  	     WHERE  MATCHES (p.project_code, l.link_code) > 0)
  7  /

7 rows updated.

SCOTT@orcl_11g> UPDATE links l
  2  SET    l.siteb =
  3  	    (SELECT MIN (p.project_code) KEEP
  4  		      (DENSE_RANK FIRST ORDER BY INSTR (l.link_code, p.project_code))
  5  	     FROM   projects p
  6  	     WHERE  MATCHES (p.project_code, l.link_code) > 0
  7  	     AND    p.project_code <> l.sitea)
  8  /

7 rows updated.


-- results:
SCOTT@orcl_11g> SELECT * FROM links
  2  /

LINK_CODE                                SITEA           SITEB
---------------------------------------- --------------- ---------------
11X3 O2W HUA00 ETH-1.5M001               11X3            O2W
11X8 O2U D1000 MUL0001                   11X8            O2U
11X8 O2U D1000 MUL0002                   11X8            O2U
0111111111111111V@WTF.IHATEME xxx001
6611111111111111V@WTF.YOUHATEME xxx001
5011111111111111T@WTF.WHYHATEME xxx001
0311111111111111T@WTF.ILOVEMExxx001

7 rows selected.

SCOTT@orcl_11g>

Re: Parsing and searching for each row [message #339150 is a reply to message #339094] Wed, 06 August 2008 22:00 Go to previous message
TJPokala
Messages: 17
Registered: March 2008
Junior Member
Thank you Barbara...I never knew nothing about oracle Text App functions.

Tried your query and worked like a charm.

Thank you!Appreciate!
Previous Topic: suppressing results(or overriding results)
Next Topic: using order by clause in create view
Goto Forum:
  


Current Time: Wed Dec 07 20:36:58 CST 2016

Total time taken to generate the page: 0.22997 seconds