Re: Simple SQL query help - String functions

On 24 Apr., 21:34, Tangz <thangam.m..._at_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') ... Received on Thu Apr 26 2007 - 02:12:05 CDT