Ouch. I discarded varchar2(4000) as that's too small of a size. Sounds like what you have works but it's a little too involved to build in, I think. I really don't want to go there but I think my only choice is a loop over the table and a regex. Horrible performance, I'm sure.
-------------------------------------------- Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc. http://mysecretbase.com -------------------------------------------- -----Original Message----- From: Hays, Duncan [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 5:03 AM To: CF-Talk Subject: RE: Text searching in Oracle I ran into this with some short news type items that were displayed on our web site with FileMaker/Lasso. FileMaker, Access, 4D and some other smaller db's have text or memo fields that hold 32k or more. The similar data type in Oracle 8 is varchar2(4000). So I put the body of the news record in a related child table. Items greater than 4k get spread across multiple records and I overlap what is saved by 100 characters so that if someone is searching text, phrases won't get cut up. Everything gets pasted back together for display. It's very fast. But we don't have a lot of news records and for many, the text is less than 4k. Duncan Hays http://www.peacecorps.gov/news/index.cfm -----Original Message----- From: Matt Robertson [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 1:34 PM To: CF-Talk Subject: Text searching in Oracle I have this code, that works great in Access and mySQL (see below). I can't seem to get this to work in Oracle no matter what I do. It doesn't like me searching on the PageText (type=long) field. Error is [Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected NUMBER got LONG I'm stuck with the long field type, and I can't use anything but a simple query. How can I search on a long text field in Oracle? <cfquery datasource="#request.SiteDSN#" name="Show"> SELECT filename.ID, filemane.Title FROM filename WHERE ( filename.Title LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.SearchText#%"> OR filename.PageText LIKE <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="%#form.SearchText#%"> ) </cfquery> -------------------------------------------- Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc. http://mysecretbase.com -------------------------------------------- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
