×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Hi, I have an address field (1 sin

Hi, I have an address field (1 sin

Hi, I have an address field (1 sin

(OP)
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

RE: Hi, I have an address field (1 sin

So if you have a table:

ID Something
1 House
2 Enclave
3 Tower


And you want to retrieve 'colored' text:

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

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.

RE: Hi, I have an address field (1 sin

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/isnu...
http://www.nullskull.com/faq/1737/isnumeric-in-ora...

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


RE: Hi, I have an address field (1 sin

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


RE: Hi, I have an address field (1 sin

(OP)
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

RE: Hi, I have an address field (1 sin

(OP)
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

RE: Hi, I have an address field (1 sin

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:

ID Something WordsToReturn
 1 House        1
 2 Enclave      2
 3 Tower        2
 
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.

RE: Hi, I have an address field (1 sin

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


RE: Hi, I have an address field (1 sin

(OP)
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

RE: Hi, I have an address field (1 sin

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


RE: Hi, I have an address field (1 sin

(OP)
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

RE: Hi, I have an address field (1 sin

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


RE: Hi, I have an address field (1 sin

(OP)
OK John, let me try

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close