[ Attachment(s) from nguyentrungtien_234 included below]
Message from: nguyentrungtien_234
I intend to write procedures/functions in PL/SQL to [b]parse Name[/b]
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 http://toadfororacle.com/servlet/JiveServlet/download/20107/Parsed.gif 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 [b]ParseAddress[/b] with the requirement as: 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!
Historical Messages
Author: nguyentrungtien_234
Date: Wed Jan 25 03:30:27 PST 2012
I intend to write procedures/functions in PL/SQL to [b]parse Name[/b]
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 http://toadfororacle.com/servlet/JiveServlet/download/20107/Parsed.gif 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 [b]ParseAddress[/b] with the requirement as: 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!
__
Perhaps you can identify where you think things are going wrong. It sounds like
you’re fine on the logic – if you can code a process in one
language, you can certainly do that in another. The trick is in learning the
punctuation/structure/etc. of the “other” language.
I could just assume you have zero knowledge on PL/SQL and point you towards
introductory courses and/or books. But that could very well be incorrect.
Perhaps you could identify specific errors you are seeing when you compile your
PL/SQL code.
I intend to write procedures/functions in PL/SQL to parse
Name field into various fields : Title, FirstName, MiddleName,
LastName, Gender
Sounds like you have a database that has not been designed correctly.
The name filed appears to hold multiple data items when, under correct
normalisation, it should contain only one.
Your table should contain a separate column for each of those separate
data items, and the Title should most likely also be in a separate title
table to ensure that you have all the same values in each Mr or Mrs etc
rows.
However, given that you may not be able to redesign the database, you
will need to give us a bit more information on what errors you are
getting, if any, or what it is that is not working correctly etc.
If this is for a one-time process and is just to get the job done and not to
learn from it, and if your table isn’t huge, you could use a combination
of dumping the data into Excel, parsing it there to separate it into the fields,
and then pull it back into TOAD using the Import Data feature and write your
code to figure out what the appropriate title or gender should be. If you
aren’t super familiar with using the substr and instr functions, this
might be easier. . .
Hopefully the data pieces are delimited consistently otherwise it could be a
mess trying to figure out which piece is which if you have missing elements. For
example, if the fields are separated by spaces, are there two spaces between
first and last name if the middle name is missing?
There is no need to do this using PL/SQL unless you really want to. You can do
some fancy footwork in SQL and do all the parsing your heart desires. J
Sounds like you have a database
that has not been designed correctly.
Well... giving the situation the benefit of doubt, the data source could be a flat file from an external party. After all... the person did mention authoring the parsing in Visual Basic.
True... could be using MS Access against an Oracle database... or MS Access against shudder MS SQL Server so you could be right after all
you will need to give us a bit more
information on what errors you are getting
Exactly... with what little information was provided, all we can really do at the moment is draw assumptions - which could be wildly out to lunch - and offer solutions based on those assumptions which may not solve the issue actually being faced.
Roger S.
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.
Sounds like you have a database
that has not been designed correctly.
Well... giving the situation the benefit of doubt, the data source could be a flat file from an external party. After all... the person did mention authoring the parsing in Visual Basic.
I see what you mean. I was obviously misled by the first sentence - "I
intend to write procedures/functions in PL/SQL to [b]parse
Name[/b] field into various fields :"
Mind you, it was difficult to read as it appeared in my inbox as one
long single paragraph.
