Querying on a long 1

[jstreich]

I'm working on a CMS, among a lot of other things, but one thing that I want to do is to query the database content of "pages" for links to a specific page.

The content is stored in a long, and contains HTML. So, would like to do something like:

select pageid from page where content like '%<a href="www.example.com/%/3%'

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]

 

[SantaMufasa]

JS,

What version of Oracle are you using? (Since you are posting in the Oracle 8i forum, I'm guessing that you are not using Oracle 9i.)

If you are using Oracle 9i, you can instantly modify your LONG column to a CLOB column and then treat that column as though it is a VARCHAR2 column:

SQL> create table js (x long);

Table created.

SQL> insert into js values ('hello');

1 row created.

SQL> alter table js modify x clob;

Table altered.

SQL> insert into js values ('good bye');

1 row created.

SQL> select * from js where x like 'hell%';

X
-------------------------------------------
hello

If you are using unsupported Oracle 8i, the question is, "Why do you not upgrade to at least Oracle 9i (since Oracle 11g is now in vogue)?"

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jstreich]

It's a 10g server. Your suggestion worked like a charm. Star for you!

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]

 

[SantaMufasa]

Thanks, JS, for the

.

Out of curiosity, why did you post your question in the Oracle 8i forum if you are using Oracle 10g?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]