1 Replies - 1847 Views - Last Post: 20 February 2013 - 11:59 AM Rate Topic: -----

#1 mossman65  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 19-September 11

Text search question

Posted 20 February 2013 - 10:36 AM

Hi Everyone,

I built a search ages ago using what i thought to be a trim function to search on everything after a specified character. I can't seem to find the right function for this.

I have a series of part numbers that prefix with 1 or 2 characters and then a - then the following numbers (there are always 5 trailing digits)

I want our sales team to be able to come in and type in the last 5 digits and bring back the results matching on those five digits alone. Would some one please give me an example, or tell me what function i should be using?

Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Text search question

#2 Craig328  Icon User is online

  • I make this look good
  • member icon

Reputation: 1926
  • View blog
  • Posts: 3,471
  • Joined: 13-January 08

Re: Text search question

Posted 20 February 2013 - 11:59 AM

Well, if it's a search against items in a database it'd employ a WHERE statement like:
WHERE partNumber LIKE '%#form.last5DigitsTheSalesGuyIsSearchingFor#'

Notice the "%" at the beginning. That's a wild card operator and that and lacking one at the end of the search term means it'll bring back all exact matches of the part number where the variable matches the end of the part number.

Now, if you were talking about comparing the supplied 5 digits to search by against some other kind of data structure (like items in an array for instance) you'd use a combination of the Trim(), Right() and either a straight CFIF equivalenct test or the FindNoCase() functions.

The concept goes like this:

1./ The string you're searching against (which we'll call "targetString") gets Trimmed so as to remove leading and trailing spaces: <cfset targetString = Trim(targetString)>

2./ Since you want to search only against the last 5 character positions of the target string, use the Right() function to get that: <cfset lastFive = Right(targetString, 5)>

3./ Finally, do a straight equivalency comparison (<cfif form.searchFive EQ lastFive>) or, if you prefer and if there might be alpha characters that could be capitalized, use the FindNoCase function (<cfif FindNoCase(form.searchFive, lastFive)>).

You can, of course combine all of that into a single line (and add a little cleanup on the supplied form variables as well) like so:
<cfif FindNoCase(Trim(form.searchFive), Right(Trim(targetString),5))>

FindNoCase returns a numeric value. 0 if the substring isn't found in the target string and the starting character position if it is found.

Hope that answers your question. Good luck!

This post has been edited by Craig328: 20 February 2013 - 12:00 PM

Was This Post Helpful? 2
  • +
  • -

Page 1 of 1