Well, I don't think it's that simple.
Inserting %s between characters of a string is no problem (loop over
the string from 1 to Len(string) using the Mid function). But this
is moot, because it won't do the job.
The search string you propose to use:
'1%2%3%4%5%6%'
will find parts:
12AB3456
1AB23456
123AB456
but it will also find:
1123AB45
1523AB456
Think about it... '1%2' means
1 *anything* 2
where *anything* is any combination of letters and numbers
1 2345678901234567890123456 2 matches
1 6464646464646464646464646 1 does not
AFAIK, your choices are:
T/SQL (MS-SQL, Sybase)
Scalar Functions (CF or db specific, if available)
denormalization
Maintaining a second search_part field need not be a pain...
There are probably only 1 or two CF templates that actually insert or
update the part_number field. These can be amended to insert/update
the companion search_part field.
You can easily generate the search_part with something like:
<cfset search_part = ReReplace(part_number, "[^0-9]", "", "ALL")>
For example:
<cfset Parts_list = "12AB3456,1AB23456,123AB456">
<cfloop index=part_number list=#Parts_list#>
<cfset search_part = ReReplace(part_number, "[^0-9]", "", "ALL")>
<cfoutput><br>#part_number#...#search_part#</cfoutput>
</cfloop>
Yields:
12AB3456...123456
1AB23456...123456
123AB456...123456
Finally, the technique of carrying an additional "reference" or
"noun" field in a parts db is quite common. This is especially true
for electronic parts where the part number often contains the
resistance, capacitance, etc. of the part; and other designations
represent the level of testing, wattage, plating, etc.
a) 123ABC100K ....... is a part (a 100,000 ohm resistor)
b) 123ABC100KMQ...... is the same part, but tested for Mil-Qual Spec
you can substitute b for a, but not vice versa
My point is that this problem existed, and was resolved, long before
there were any databases (punched card systems), by carrying a
reference part number or noun (used for sorting, selecting).
I think that the simplest way to implement this with a database is to
carry forward the technique, and use an additional search_part number.
The simplest way is usually the best way...
Dick
At 12:08 AM -0500 5/16/2000, Jonathan Karlen wrote:
>Well - its not that I want to ignore the non-numeric groups, we're giving
>the user the option to either search for an exact part number, a substring
>or by part family (yes, it is an electronic parts database).
>
>Creating a second field would be a serious pain as far as maintanence goes.
>
>In many languages I could store the search string as an array and loop
>through the array inserting '%' symbols in between each character. I'm not
>aware of any CF way to do this.
>
>Jon
>
>
>----- Original Message -----
>From: Dick Applebaum <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Monday, May 15, 2000 10:34 PM
>Subject: Re: Replacing Letters in a String
>
>
>> mmm...
>>
>> Are you sure you want to ignore *any* number of non-numeric groups?
>>
>> If so, one way would be denormalization... storing a second copy of
>> the part number in the table. This second part number would
>> represent the part number in a format that could be readily searched:
>>
>> Real Part # Search Part #
>> -------------- --------------
> > 12AB3456 123456
>> 1AB23456 123456
>> 123AB456 123456
> >
>>
>> Another way would be to use T/SQL statements, if the db is MS-SQL 7.
>>
>> I would tend to recommend the denormalization... at the cost of a few
>> bytes in each record you greatly simplify the search process.
>>
>> The input search field can be manipulated with regexp to easily
>> eliminate the non-numerics.
>>
>> This isn't an electronics parts db, by any chance, it it>
>>
>>
>> HTH
>>
>> Dick
>>
>>
>> At 10:42 PM -0500 5/15/2000, Jonathan Karlen wrote:
>> >This is a multi-part message in MIME format.
> > >
>> >------=_NextPart_000_0056_01BFBEBE.CE241C80
>> >Content-Type: text/plain;
>> > charset="iso-8859-1"
>> >Content-Transfer-Encoding: quoted-printable
>> >
>> >Greetings -
>> >
>> >I am developing a parts database for a client which is keyed by part =
>> >number. He wants to be able to search by first numeric sequence. So, =
>> >for example, a search for 123456 would return part numbers 12AB3456, =
>> >1AB23456, 123AB456 and so on. =20
>> >
>> >>From a SQL point of view what I'd imagine I need to generate is a string
>=
> > >like "1%2%3%4%5%6%" to allow for characters between each number.
>> >
>> >Does anyone have any suggestions of how I can generate this string based
>=
>> >on user input?
>> >
>> >Thanks.
>> >
>> >Jonathan Karlen
>> --------------------------------------------------------------------------
>----
>> Archives: http://www.eGroups.com/list/cf-talk
>> To Unsubscribe visit
>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
>send a message to [EMAIL PROTECTED] with 'unsubscribe' in
>the body.
>>
>
>------------------------------------------------------------------------------
>Archives: http://www.eGroups.com/list/cf-talk
>To Unsubscribe visit
>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_tal
>k or send a message to [EMAIL PROTECTED] with
>'unsubscribe' in the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
