Hi, I have an address field (1 sin

[rk68]

Hi,
I have an address field (1 single field). I have pull out/insert in another fields certain text from the address fields matching to various criteria.
e.g the address field are
a) 88-C DGP House Prabhadevi Mumbai 400025
b) 405 Prathmesh Ashish Enclave Kanakia Santacruz Mumbai 400054
c) Plot # 78-A Ostwal Orchid Tower Prabhadevi Mumbai 25

Output or text in another field will be
a) DBP House
b) Prathmesh Ashish Enclave
c) Ostwal Orchid Tower

A separate table will have matching criteria (like in above e.g. House, Enclave & Tower.
So wherever the matching criteria fields match with address table the text before the matching field should be extracted.
How can this be done - thru a SQL or procedure. I tried using REGEXP_SUBSTR, but wasn't of much help.

TIA,
RAJ

 

[Andrzejek]

So if you have a table:
[tt]
ID Something[green]
1 House
2 Enclave
3 Tower[/green]
[/tt]
And you want to retrieve 'colored' text:
[tt]
88-C [blue]DGP[/blue] [green]House[/green] Prabhadevi Mumbai 400025
405 [blue]Prathmesh Ashish[/blue] [green]Enclave[/green] Kanakia Santacruz Mumbai 400054
Plot # 78-A [blue]Ostwal Orchid[/blue] [green]Tower[/green] Prabhadevi Mumbai 25
[/tt]
We know where you want STOPing place to be - after green text from the table above.
But what is the rule of where you want STARTing place?


---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

There are several options. If your list of matching fields is 'small', you can use the Oracle INSTR command to search each string for the matching criteria. If the list is 'long', you may want to put the matching criteria in a table. The threshold for small/long will depend on your system, but for a small list, the IF statements will be more efficient that the table lookup (SELECT). In addition, the table option does allow for easier changes.

You would need to parse the incoming string using INSTR and space as a delimiter, then check each parsed string (word) against the matching criteria table. You also need to determine the string that is the last numeric. This link will help with that:

https://www.techonthenet.com/oracle/questions/isnumeric.php

http://www.nullskull.com/faq/1737/isnumeric-in-oracle-database.aspx

Initialize the numeric position counter to zero.
So, use INSTR to find the first phrase/word.
Check to see if it is numeric. If yes, save the position counter.
Go to the next phrase/word (increment the counter for INSTR).
If the position counter for numeric is not zero, use SQL to check for a match criteria.
Select matched_phrase if exists (select 1 from matching_criteria_table mct where string_to_be_checked = mct.matching_criteria);
etc




==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[johnherman]

To further Andy's question, what if they live in 1234 Enclave Tower? 2 words are matching?

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[rk68]

Thanks Andy & John.

with reference to John's query
"To further Andy's question, what if they live in 1234 Enclave Tower? 2 words are matching?"
it should pick the first matching work (actually will need both).
I'm yet to look into John's link.

I did try something like this but output not quite satisfying:
select t.cnt_firm_add1,
REGEXP_SUBSTR (t.cnt_firm_add1, 'House|Bldg|Apt|Tower|Residency|Enclave', 1),
SUBSTR(t.cnt_firm_add1,INSTR(t.cnt_firm_add1,' ')+1,
INSTR(t.cnt_firm_add1,REGEXP_SUBSTR (t.cnt_firm_add1, 'House|Bldg|Apt|Tower|Residency|Enclave',1))-2-INSTR(t.cnt_firm_add1,' ')+1) x1
from t_dxcc_contact_mst t

 

[rk68]

Referring to ANdy's query:
"We know where you want STOPing place to be - after green text from the table above.
But what is the rule of where you want STARTing place?"

Yes you are right, we know the stopping point.
Starting point would be the word before & 2 words before the matching criteria.
I was planning like to add 2-3 fields & matching criteria would fall in another field
So in the below example, House will be stored in the criteria field, DBG in field 2
Like wise Enclave in criteria field, Prathmesh in field1, Ashish in field 2

88-C DGP House Prabhadevi Mumbai 400025
405 Prathmesh Ashish Enclave Kanakia Santacruz Mumbai 400054
Plot # 78-A Ostwal Orchid Tower Prabhadevi Mumbai 25

 

[Andrzejek]

If your rule is to return 1 word from the address with House, and 2 words from address containing Enclave or Tower, I can see this table:

[pre]
ID Something WordsToReturn
1 House 1
2 Enclave 2
3 Tower 2
[/pre]
And a little User Defined Function where you pass your address field, this function detects the word (House, Enclave, Tower, etc.), looks up how many words to return (from this new table) and returns what you need.


---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

While it may be possible to do this in a single SQL statement, this looks like something that needs to be within one or two PL/SQL loops.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[rk68]

Hi,
Can this query be tweaked to get the 1st word before the matching word.
e.g. '88 C DBP HOUSE PRABHADEVI', I would want just "DBP" as "House" is matching.

The below SQL gives "C DBP"

select SUBSTR('88 C DBP HOUSE PRABHADEVI',INSTR('88 C DBP HOUSE PRABHADEVI',' ')+1,
INSTR('88 C DBP HOUSE PRABHADEVI',REGEXP_SUBSTR ('88 C DBP HOUSE PRABHADEVI','HOUSE|BLDG|APT',1))-2-INSTR('88 C DBP HOUSE PRABHADEVI',' ')+1) a1
from DUAL

I tried but couldn't figure out.

Thanks

 

[johnherman]

You wrote this expression to match the keyword.

INSTR(t.cnt_firm_add1,REGEXP_SUBSTR (t.cnt_firm_add1, 'House|Bldg|Apt|Tower|Residency|Enclave',1))-2-INSTR(t.cnt_firm_add1,' ')+1) x1
from t_dxcc_contact_mst t

Subtract 1 from this gives the location of the previous space character.
Subtracting 2 gives the location of the end of the previous word.

So, the location of the start of the previous word is x1 minus 2.
"Trim" the input string to exclude the space, match, and rest of the string.

Now we need the location of the last space, as the last word will be one byte after it.
So, we need to loop through using INSTR to find each space and save off the position of the last space.
Then add 1 for the start of the next word.


==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[rk68]

Hi,
While executing the below query, I get the result shown below

select trim(t.address) addr,
substr(' '||trim(t.address),INSTR(' '||trim(t.address),' ')+1,
INSTR(' '||trim(t.address),REGEXP_SUBSTR (' '||trim(t.address),'HEIGHTS',1))-2-INSTR(' '||trim(t.address),' ')+1) a1
from t_tata1_mst t

Output:
ADDR: BLOCK NO - 103, DARSHAN HEIGHTS, OPP. DEEPAK THEATRE
A1: BLOCK NO - 103, DARSHAN

The query is listing out all the words before the word "HEIGHTS".
My requirement is that A1 should only display "DARSHAN" only, i.e. only the word before the "HEIGHTS".


Thanks,
RAJ

 

[johnherman]

as I mentioned, if you do this in a PL/SQL loop, then the loop index will help you identify the "last" word before the keyword.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[rk68]

OK John, let me try