database design.
The number of prefixes you'll need to deal with is endless.
Fix your tables instead of fighting symptoms.
On 24 Apr., 21:34, Tangz <thangam.m
@gmail.com> wrote:
> I need a select query that selects the first, last and middle name of
> employees without hyphens, apostrophes, blanks, periods, suffixes
> (Jr.) or prefix (Dr.). My current query looks something like this:
> Select Fname, Lname, Mname
> from employee
> where <condition>
On first glance I would say that this goes beyond simple string
manipulation. Hence you might consider writing a stored function and
call that:
Select StripNameFuzz(Fname), StripNameFuzz(Lname),
StripNameFuzz(Mname)
from employee
where <condition>
StripNameFuzz would get the dirty string and return the cleaned one. I
think I would scan it char by char using substr to be able to handle
complex situations.
But maybe this suffices in your case:
replace(Lname,'Dr. ', ' ') to remove 'Dr.'
replace(Lname,' Jr.', ' ') to remove 'Jr.'
translate(LName,'''" .', 'aea') to remove and replace special
chars
You see there is not much logic in there. 'Dr.' must be followed by
blank to be recognized for instance.
Combining the string functions it becomes something like this:
select translate(replace(replace(Lname,'Dr. ', ' '),' Jr.', '
'),'''" .', 'aea') ...
-----------------------------------------------Reply-----------------------------------------------
Thorsten Kettner wrote:
> select translate(replace(replace(Lname,'Dr. ', ' '),' Jr.', '
> '),'''" .', 'aea') ...
Assuming it doesn't contain "Dr" without the period, or "MD PhD" or M.D.
Ph.D" and a near infinite supply of other variants. Often the job is
hopeless unless the table is redesigned to guarantee integrity.
--
Daniel A. Morgan
University of Washington
damor
@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
