Re: Simple SQL query help - String functions

On 24 Apr., 21:34, Tangz <thangam.m...@xxxxxxxxx> 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') ...

.

Relevant Pages

  • Re: Gui Question
    ... employee they wish to create and specify the data concerning that ... private String firstName; ... public class CommissionEmployee extends Employee ... public class BasePlusCommissionEmployee extends CommissionEmployee ...
    (comp.lang.java.help)
  • Re: inheritance question
    ... the constructor of the Employee class should accept all parameters ... public Employee (DateTime DOB, string Address, string MaritalStatus, ... > private string mMaritalStatus; ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Need VBA Help to Append!
    ... marks to delimit your string and date fields. ... Notice single quotes around the employee name and Hash marks around the ... Dim strSQL As String ...
    (microsoft.public.access.formscoding)
  • Re: Class fog in my brain
    ... suppose you have a UDT that has data pertaining ... to an employee in your latest HR manager program. ... Then give it a public function called FullName: ... Public Function FullNameAs String ...
    (microsoft.public.vb.general.discussion)
  • Re: Sample code for random access file io program
    ... int iRecordNumber; ... In any case, what you probably should do is use a byte array, set up for the exact size of the record, then for each employee oject you add a method that returns a byte array representation of the object. ... Part of the problem is that the Name string is variable length and I ...
    (comp.lang.java.programmer)