RE: Constrain on a blob
Thanks for the input all. It appears you can add a 1,1 attribute on the end
to define the start? and span? seems to work more like a wildcard. Still
cant get it case insensitive
-Original Message-
From: Plunkett, Matt [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 4:14 PM
To: CF-Talk
Subject: RE: Constrain on a blob
Hrm, I see the problem. My bad. I looked around on google a bit, but
didn't see any answers beyond using an Oracle Text index on the field.
-Original Message-
From: Deanna Schneider [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 2:07 PM
To: CF-Talk
Subject: Re: Constrain on a blob
No, it doesn't work, dbms_lob is a package and INSTR is a function within
the package. It does not support UPPER.
-Deanna
- Original Message -
From: "Plunkett, Matt" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, October 15, 2003 8:52 AM
Subject: RE: Constrain on a blob
> To do case insensitive like searches, I use UPPER() on both sides of the
> where clause. I haven't tried it here, but WHERE
> dbms_lob.INSTR(UPPER(section.text), UPPER('abc'))>0 may work fine.
>
> -Original Message-
> From: Deanna Schneider [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 15, 2003 9:39 AM
> To: CF-Talk
> Subject: Re: Constrain on a blob
>
>
> You should be able to do this (at least it works on clobs)
> SELECT header FROM section WHERE dbms_lob.INSTR(section.text, 'abc')>0
>
> This performs a case-sensitve LIKE search. I've yet to find any way to do
a
> case-insensitive LIKE search.
>
> -Deanna
>
> - Original Message -
> From: "Sean McCarthy" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Tuesday, October 14, 2003 6:07 PM
> Subject: Constrain on a blob
>
> > I get and 'inconsistent datatype' error when I try to:
> >
> > SELECT
> > title
> > FROM
> > myTable
> > WHERE
> > myBlob = 'abc'
> >
> > Is it possible to constrain on the information in a blob. Optimally I
> would
> > like the ability to do a 'like %foo%'
> >
> _
>
>
>
_
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Constrain on a blob
Hrm, I see the problem. My bad. I looked around on google a bit, but
didn't see any answers beyond using an Oracle Text index on the field.
-Original Message-
From: Deanna Schneider [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 2:07 PM
To: CF-Talk
Subject: Re: Constrain on a blob
No, it doesn't work, dbms_lob is a package and INSTR is a function within
the package. It does not support UPPER.
-Deanna
- Original Message -
From: "Plunkett, Matt" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, October 15, 2003 8:52 AM
Subject: RE: Constrain on a blob
> To do case insensitive like searches, I use UPPER() on both sides of the
> where clause. I haven't tried it here, but WHERE
> dbms_lob.INSTR(UPPER(section.text), UPPER('abc'))>0 may work fine.
>
> -Original Message-
> From: Deanna Schneider [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 15, 2003 9:39 AM
> To: CF-Talk
> Subject: Re: Constrain on a blob
>
>
> You should be able to do this (at least it works on clobs)
> SELECT header FROM section WHERE dbms_lob.INSTR(section.text, 'abc')>0
>
> This performs a case-sensitve LIKE search. I've yet to find any way to do
a
> case-insensitive LIKE search.
>
> -Deanna
>
> - Original Message -
> From: "Sean McCarthy" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Tuesday, October 14, 2003 6:07 PM
> Subject: Constrain on a blob
>
> > I get and 'inconsistent datatype' error when I try to:
> >
> > SELECT
> > title
> > FROM
> > myTable
> > WHERE
> > myBlob = 'abc'
> >
> > Is it possible to constrain on the information in a blob. Optimally I
> would
> > like the ability to do a 'like %foo%'
> >
> _
>
>
>
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Constrain on a blob
No, it doesn't work, dbms_lob is a package and INSTR is a function within
the package. It does not support UPPER.
-Deanna
- Original Message -
From: "Plunkett, Matt" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, October 15, 2003 8:52 AM
Subject: RE: Constrain on a blob
> To do case insensitive like searches, I use UPPER() on both sides of the
> where clause. I haven't tried it here, but WHERE
> dbms_lob.INSTR(UPPER(section.text), UPPER('abc'))>0 may work fine.
>
> -Original Message-
> From: Deanna Schneider [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 15, 2003 9:39 AM
> To: CF-Talk
> Subject: Re: Constrain on a blob
>
>
> You should be able to do this (at least it works on clobs)
> SELECT header FROM section WHERE dbms_lob.INSTR(section.text, 'abc')>0
>
> This performs a case-sensitve LIKE search. I've yet to find any way to do
a
> case-insensitive LIKE search.
>
> -Deanna
>
> - Original Message -
> From: "Sean McCarthy" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Tuesday, October 14, 2003 6:07 PM
> Subject: Constrain on a blob
>
> > I get and 'inconsistent datatype' error when I try to:
> >
> > SELECT
> > title
> > FROM
> > myTable
> > WHERE
> > myBlob = 'abc'
> >
> > Is it possible to constrain on the information in a blob. Optimally I
> would
> > like the ability to do a 'like %foo%'
> >
> _
>
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Constrain on a blob
To do case insensitive like searches, I use UPPER() on both sides of the
where clause. I haven't tried it here, but WHERE
dbms_lob.INSTR(UPPER(section.text), UPPER('abc'))>0 may work fine.
-Original Message-
From: Deanna Schneider [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 15, 2003 9:39 AM
To: CF-Talk
Subject: Re: Constrain on a blob
You should be able to do this (at least it works on clobs)
SELECT header FROM section WHERE dbms_lob.INSTR(section.text, 'abc')>0
This performs a case-sensitve LIKE search. I've yet to find any way to do a
case-insensitive LIKE search.
-Deanna
- Original Message -
From: "Sean McCarthy" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, October 14, 2003 6:07 PM
Subject: Constrain on a blob
> I get and 'inconsistent datatype' error when I try to:
>
> SELECT
> title
> FROM
> myTable
> WHERE
> myBlob = 'abc'
>
> Is it possible to constrain on the information in a blob. Optimally I
would
> like the ability to do a 'like %foo%'
>
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Constrain on a blob
You should be able to do this (at least it works on clobs) SELECT header FROM section WHERE dbms_lob.INSTR(section.text, 'abc')>0 This performs a case-sensitve LIKE search. I've yet to find any way to do a case-insensitive LIKE search. -Deanna - Original Message - From: "Sean McCarthy" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, October 14, 2003 6:07 PM Subject: Constrain on a blob > I get and 'inconsistent datatype' error when I try to: > > SELECT > title > FROM > myTable > WHERE > myBlob = 'abc' > > Is it possible to constrain on the information in a blob. Optimally I would > like the ability to do a 'like %foo%' > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Constrain on a blob
Shouldn't you be storing the data in a CLOB or TEXT field if it's text? afaik BLOB = binary large object, not reallly where you want to be storing text. -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: 15 October 2003 01:26 To: CF-Talk Subject: Re: Constrain on a blob Ya you need to convert it via a functionand for the life of me I can't remember what it's called (just found it a month ago)...try Google..."Oracle 9 SELECT BLOB"should point ya in the right direction ;-) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com - Original Message - From: Sean McCarthy To: CF-Talk Sent: Tuesday, October 14, 2003 4:37 PM Subject: RE: Constrain on a blob its an oracle 9 db and im working in toad (fancy sql plus) rite now seems like i need to perform some sort of convert method on the data first? -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 7:37 PM To: CF-Talk Subject: Re: Constrain on a blob What DB and what version of CF? CF 5 and earlier cannot select a BLOBbut you can get some Java based custom tags to do it Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com - Original Message - From: Sean McCarthy To: CF-Talk Sent: Tuesday, October 14, 2003 4:07 PM Subject: Constrain on a blob I get and 'inconsistent datatype' error when I try to: SELECT title FROM myTable WHERE myBlob = 'abc' Is it possible to constrain on the information in a blob. Optimally I would like the ability to do a 'like %foo%' _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Constrain on a blob
Ya you need to convert it via a functionand for the life of me I can't remember what it's called (just found it a month ago)...try Google..."Oracle 9 SELECT BLOB"should point ya in the right direction ;-) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com - Original Message - From: Sean McCarthy To: CF-Talk Sent: Tuesday, October 14, 2003 4:37 PM Subject: RE: Constrain on a blob its an oracle 9 db and im working in toad (fancy sql plus) rite now seems like i need to perform some sort of convert method on the data first? -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 7:37 PM To: CF-Talk Subject: Re: Constrain on a blob What DB and what version of CF? CF 5 and earlier cannot select a BLOBbut you can get some Java based custom tags to do it Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com - Original Message - From: Sean McCarthy To: CF-Talk Sent: Tuesday, October 14, 2003 4:07 PM Subject: Constrain on a blob I get and 'inconsistent datatype' error when I try to: SELECT title FROM myTable WHERE myBlob = 'abc' Is it possible to constrain on the information in a blob. Optimally I would like the ability to do a 'like %foo%' _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Constrain on a blob
its an oracle 9 db and im working in toad (fancy sql plus) rite now seems like i need to perform some sort of convert method on the data first? -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 7:37 PM To: CF-Talk Subject: Re: Constrain on a blob What DB and what version of CF? CF 5 and earlier cannot select a BLOBbut you can get some Java based custom tags to do it Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com - Original Message - From: Sean McCarthy To: CF-Talk Sent: Tuesday, October 14, 2003 4:07 PM Subject: Constrain on a blob I get and 'inconsistent datatype' error when I try to: SELECT title FROM myTable WHERE myBlob = 'abc' Is it possible to constrain on the information in a blob. Optimally I would like the ability to do a 'like %foo%' _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Constrain on a blob
What DB and what version of CF? CF 5 and earlier cannot select a BLOBbut you can get some Java based custom tags to do it Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com - Original Message - From: Sean McCarthy To: CF-Talk Sent: Tuesday, October 14, 2003 4:07 PM Subject: Constrain on a blob I get and 'inconsistent datatype' error when I try to: SELECT title FROM myTable WHERE myBlob = 'abc' Is it possible to constrain on the information in a blob. Optimally I would like the ability to do a 'like %foo%' [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
