Michael, I get the same results when I execute the sql statement in SQL*Plus.
The first row is Adam instead of Adam-salmi. On Jul 27, 4:09 pm, John H <[email protected]> wrote: > 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 > > > > > [email protected] > > > > 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 <[email protected]> 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 [email protected] > > > > > To unsubscribe from this group, send email to > > > > > [email protected] > > > > > For more options, visit this group at > > > > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hidequoted 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 [email protected] > > > To unsubscribe from this group, send email to > > > [email protected] > > > For more options, visit this group at > > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hide quoted text - > > > - Show quoted text -- 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
