substring(name,1,CHAR_LENGTH(name)-5) If the name was 123456a.jpg, this would give you 123456.
I hate to be the bad guy but for the record, your troubles stem from a poor database design. Ideally you shouldn't have to resort to string manipulation to tie a record in your photo table to the MLS number it is associated with. I don't know what columns you have in your photos table, but one of them needs to be the MLS number (or whatever other unique key you might use to specify a listing). The column should have an index on it and probably have a foreign key constraint to your master listing table. It is not possible for you to properly use an index right now on your name column if you are performing string manipulation on it. Performance might suffer if you have a large number of records in that table since every record must be scanned, and the substring must be performed each value to find the matching ones. Also, where is the GetAll query coming from? If it is a select being run against the same data source, why not do a join and get your list in a single query? What do you do with old listings? Do you delete them from whatever table they are stored, or do you set some status column? ~Brad -------- Original Message -------- Subject: RE: using CF to eliminate old Real Estate photos From: "Dave Long" <d...@northgoods.com> Date: Tue, July 21, 2009 4:41 pm To: cf-talk <cf-talk@houseoffusion.com> Thanks for the info, Rick but unfortunately, the MLS numbers currently vary between five and six digits. If that Real Estate market stays this bad, they may go to seven or eight digit numbers. However, is it possible to create a list from the query "Photos" and Remove a character from the right side of the field? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324785 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
