Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Problem with Parsing Address & Name, deal with parsing the address field
tiennt
post Jan 25 2012, 05:47 AM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 25-January 12
Member No.: 46,652



I intend to write procedures/functions in PL/SQL to parse Name field into various fields : Title, FirstName, MiddleName, LastName, Gender
E.g:
Nguyen Van A
Nguyen Thi B

After parsing, the result will be shown as:

Title FirstName MiddleName LastName Gender
Mr Nguyen Van A Male
Ms Nguyen Thi B Female
Attached File  Parsed.gif ( 4.94K ) Number of downloads: 2


I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender = "F". Otherwise, Title = "Mr", and Gender = "M".


2/ Another procedure/function is [i]ParseAddress with the requirement as:[/i]
Address field is divided into Street, Group, Area, Ward, County fields
E.g.:
No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London

The result:

Street Group Area Ward County
No 6 Sum Street Group 8 Area 2 ABCD London

I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->it doesn't work. Please give me insructions. Thank you very much for your support!
Kind regards!
Go to the top of the page
 
+Quote Post
dvsoukup
post Jan 25 2012, 11:31 AM
Post #2


Newbie
*

Group: Members
Posts: 7
Joined: 13-January 12
Member No.: 46,588



QUOTE (tiennt @ Jan 25 2012, 02:47 AM) *
I intend to write procedures/functions in PL/SQL to parse Name field into various fields : Title, FirstName, MiddleName, LastName, Gender
E.g:
Nguyen Van A
Nguyen Thi B

After parsing, the result will be shown as:

Title FirstName MiddleName LastName Gender
Mr Nguyen Van A Male
Ms Nguyen Thi B Female
Attached File  Parsed.gif ( 4.94K ) Number of downloads: 2


I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender = "F". Otherwise, Title = "Mr", and Gender = "M".


2/ Another procedure/function is [i]ParseAddress with the requirement as:[/i]
Address field is divided into Street, Group, Area, Ward, County fields
E.g.:
No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London

The result:

Street Group Area Ward County
No 6 Sum Street Group 8 Area 2 ABCD London

I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->it doesn't work. Please give me insructions. Thank you very much for your support!
Kind regards!



Hey there! Go read up on substr() and instr() functions. In string functios returns the first matching occurance of a character/set of characters. Use that in conjuntion with substring to determine the starting position of a word and for 'how far' to parse it for. Doing this you can extract each word. Here's an example:

select SUBSTR('The Fatty Cats', 1, INSTR('The Fatty Cats', ' ', 1, 1) -1) as FIRST_WORD,
SUBSTR('The Fatty Cats', INSTR('The Fatty Cats', ' ', 1, 1), INSTR('The Fatty Cats', ' ', 1, 2) - INSTR('The Fatty Cats', ' ', 1, 1) ) as SECOND_WORD,
SUBSTR('The Fatty Cats', INSTR('The Fatty Cats', ' ', 1, 2)) as THIRD_WORD from DUAL;

Now in the case of PL/SQL, youd would merely do some sort of loop, while setting some variable to the names you're using to use that as the string. In this example I posted however, it would be assuming you had specifically 3 words to parse out. This would not work for something that needed to be dynamic (ie a name being passed in that had 5 spaces in it as opposed to 4...). But once you grasp the instr/substr technique, you should be able to make something dynamic w/o a problem smile.gif

Anyhoo hope that didn't confuse you to much... Just go read up on substring and instring and I think you'll be on the right track of what you want to do.

You could also research regexp() as that would work as well...but I think you'll do just fine w/ substring and instring.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd October 2014 - 04:31 AM