Rich,
I gave that a try and it gives me more than just Adam, it takes me
back to the beggining of the A's.
On Jul 27, 2:45 pm, Richard Pascual <richg...@gmail.com> wrote:
> Hi John,
>
> In that case, here's something that I have found useful in my applications
> that I have programmed which can do partial searches. Instead of >=, as in:
>
> AND LAST_NAME || FIRST_NAME >= TRIM(:WS-BEGIN-KEY02)
>
> try:
>
> AND INSTR(LAST_NAME || FIRST_NAME, TRIM(:WS-BEGIN-KEY02)) > 0
>
> The INSTR function checks the presence of the second parameter in the first
> parameter. If the second parameter turns up a partial or full match, the
> result of the INSTR function returns a non-zero value (i.e., the exact
> position in parameter 1 that parameter 2 appears)
>
> For example, some quick tests:
>
> SELECT INSTR('TEST','ES') from DUAL
> will return the value "2" because the string "ES" appears beginning in the
> second position of the search string "TEST".
>
> SELECT INSTR('TEST','ABC') from DUAL
> will return the value "0" because the string "ABC" is nowhere in the search
> field value "TEST".
>
> One issue you will encounter involves mixed cases. I usually solve that by
> casting all strings in either all upper or all lower case... which may be a
> performance issue depending on how large your database is. If you try my
> suggestion, try it without converting the case to all UPPER or LOWER case
> first.
>
> Rich
>
>
>
> On Tue, Jul 27, 2010 at 11:34 AM, John H <ho...@chesterfield.gov> wrote:
> > Rich,
>
> > This is a browse and I would like for it to show the "Adam-salmi" and
> > anything greater incase they need to browse farther. Sometimes they
> > don't type in a full name.
>
> > On Jul 27, 2:14 pm, Richard Pascual <richg...@gmail.com> wrote:
> > > Hi John,
> > > I am curious of your clause in the "where" criteria that says:
>
> > > AND LAST_NAME || FIRST_NAME >= TRIM(:WS-BEGIN-KEY02)
>
> > > The ">=" operator will include results that are not also exact matches,
> > such
> > > as in your hyphenated example: "Adam-salmi" returning "Adam"
>
> > > If you want an exact match, replace the ">=" operator with "="
>
> > > There are a number of ways to facilitate partial matches (such as using
> > > "instr" or "like" operators) but it depends on the needs of your
> > application
> > > which uses this query.
>
> > > Rich
>
> > > rgpasc...@berkeley.edu
> > > Rich Pascual
> > > Database Programmer, IT Systems Management
> > > Advancement Operations
> > > U.C. Berkeley University Relations
> > > Phone: (510) 643-7652
>
> > > On Tue, Jul 27, 2010 at 10:46 AM, John H <ho...@chesterfield.gov> wrote:
> > > > I am currently trying to write a select statement that will return
> > > > name information. We have a situtation where we have last names with
> > > > hypens in them and when the exact name is entered "Adam-salmi" it
> > > > returns last names that are "Adam" also. I am new to Oracle and any
> > > > help would be greatly appreciated. I will attach the sql statement.
>
> > > > SELECT LAST_NAME,
> > > > FIRST_NAME,
> > > > MIDDLE_NAME,
> > > > SUFFIX,
> > > > TO_CHAR(BIRTH_DATE,'YYYY/MM/DD'),
> > > > SSN_OR_EIN,
> > > > WARRANT_DEPARTMENT,
> > > > WARRANT_KEY,
> > > > PERSON_SEX,
> > > > RACE_CODE,
> > > > LAST_NAME || FIRST_NAME
> > > > FROM PR00100T.WARRANT A,
> > > > PR00100T.MASTER_NAME B,
> > > > PR00100T.PERSON C
> > > > WHERE A.WANTED_PERSON_KEY = B.PERSON_KEY
> > > > AND A.WANTED_PERSON_KEY = C.PERSON_KEY
> > > > AND A.WANTED_NAME_KEY = B.NAME_KEY
> > > > AND A.WARR_STATUS_CODE = '01'
> > > > AND LAST_NAME || FIRST_NAME >= TRIM(:WS-BEGIN-KEY02)
> > > > ORDER BY B.LAST_NAME ASC,
> > > > B.FIRST_NAME ASC,
> > > > B.MIDDLE_NAME ASC,
> > > > A.WARRANT_KEY ASC;
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > > > Groups "Oracle PL/SQL" group.
> > > > To post to this group, send email to Oracle-PLSQL@googlegroups.com
> > > > To unsubscribe from this group, send email to
> > > > oracle-plsql-unsubscr...@googlegroups.com
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hide quoted text -
>
> > > - Show quoted text -
>
> > --
> > You received this message because you are subscribed to the Google
> > Groups "Oracle PL/SQL" group.
> > To post to this group, send email to Oracle-PLSQL@googlegroups.com
> > To unsubscribe from this group, send email to
> > oracle-plsql-unsubscr...@googlegroups.com
> > For more options, visit this group at
> >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text -
>
> - Show quoted text -
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
