Oracle- need to use CONTAINS to find a string that contains a period

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I have a field in a table that is domain indexed so I can use CONTAINS in a query. I am trying to create a query that can find an acronym (i.e. J.A.V.A.) exactly. Problem is that the period '.' is a stopword that gets ignored. I need to figure out how to do this by escaping the '.' somehow. I can't find any reference online about how to do this, or maybe I am not fully understanding what I am reading.

I have tried

SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J.A.V.A}')>0;



SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J_A_V_A_}')>0;



SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J.A.V.A.}')>0;

I created the domain index by:
CREATE INDEX table_txt_idx ON table(CLOB_column) INDEXTYPE IS CTXSYS.CONTEXT;

I saw something (https://oracle-base.com/articles/9i/full-text-indexing-using-oracle-text-9i) (https://docs.oracle.com/database/121/CCREF/csql.htm#CCREF0100) about appending to the index but I don't think it applies to a ctxsys index, but I am not fully sure. I just know what I used didn't work.

BEGIN

  CTX_DLL.OPTIMIZE_INDEX('IDX_COLUMN_TXT','FAST');

END;

This didn't mention the ctxsys (https://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm#BEIIEAFD)

I have created similar queries using LIKE and REGEXP_INSTR and need to create the same using CONTAINS so I can make sure I run the one that works the quickest.

SELECT * from table WHERE CLOB_column LIKE '%J.A.V.A.%';  --4.441 seconds



SELECT * from table WHERE REGEXP_INSTR(CLOB_column, '(J.A.V.A.)')>0; --23.528 seconds

asked Nov 26 '18 at 19:22

Jennifer Crosby

235

Hi. Have a look at this answer: stackoverflow.com/a/37886431/10300113 . The whole answer is useful to you and it also contains a link to chapter 'punctuations' in 'Oracle Text Indexing Elements': docs.oracle.com/cd/E11882_01/text.112/e24436/…

– Peter
Nov 26 '18 at 19:39

Possible duplicate of Query with wildcard and dot not matching data with Oracle Text index

– Peter
Nov 26 '18 at 19:42

Great thread @Peter! I wonder why I didn't find it? Probably not using correct words in search. Thanks! I'll check this out and let you guys know if it works.

– Jennifer Crosby
Nov 28 '18 at 16:34

add a comment |

I have tried

SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J.A.V.A}')>0;



SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J_A_V_A_}')>0;



SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J.A.V.A.}')>0;

I created the domain index by:
CREATE INDEX table_txt_idx ON table(CLOB_column) INDEXTYPE IS CTXSYS.CONTEXT;

BEGIN

  CTX_DLL.OPTIMIZE_INDEX('IDX_COLUMN_TXT','FAST');

END;

This didn't mention the ctxsys (https://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm#BEIIEAFD)

I have created similar queries using LIKE and REGEXP_INSTR and need to create the same using CONTAINS so I can make sure I run the one that works the quickest.

SELECT * from table WHERE CLOB_column LIKE '%J.A.V.A.%';  --4.441 seconds



SELECT * from table WHERE REGEXP_INSTR(CLOB_column, '(J.A.V.A.)')>0; --23.528 seconds

asked Nov 26 '18 at 19:22

Jennifer Crosby

235

Hi. Have a look at this answer: stackoverflow.com/a/37886431/10300113 . The whole answer is useful to you and it also contains a link to chapter 'punctuations' in 'Oracle Text Indexing Elements': docs.oracle.com/cd/E11882_01/text.112/e24436/…

– Peter
Nov 26 '18 at 19:39

Possible duplicate of Query with wildcard and dot not matching data with Oracle Text index

– Peter
Nov 26 '18 at 19:42

Great thread @Peter! I wonder why I didn't find it? Probably not using correct words in search. Thanks! I'll check this out and let you guys know if it works.

– Jennifer Crosby
Nov 28 '18 at 16:34

add a comment |

I have tried

SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J.A.V.A}')>0;



SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J_A_V_A_}')>0;



SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J.A.V.A.}')>0;

I created the domain index by:
CREATE INDEX table_txt_idx ON table(CLOB_column) INDEXTYPE IS CTXSYS.CONTEXT;

BEGIN

  CTX_DLL.OPTIMIZE_INDEX('IDX_COLUMN_TXT','FAST');

END;

This didn't mention the ctxsys (https://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm#BEIIEAFD)

I have created similar queries using LIKE and REGEXP_INSTR and need to create the same using CONTAINS so I can make sure I run the one that works the quickest.

SELECT * from table WHERE CLOB_column LIKE '%J.A.V.A.%';  --4.441 seconds



SELECT * from table WHERE REGEXP_INSTR(CLOB_column, '(J.A.V.A.)')>0; --23.528 seconds

asked Nov 26 '18 at 19:22

Jennifer Crosby

235

I have tried

SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J.A.V.A}')>0;



SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J_A_V_A_}')>0;



SELECT * FROM table WHERE CONTAINS(CLOB_column, '{J.A.V.A.}')>0;

I created the domain index by:
CREATE INDEX table_txt_idx ON table(CLOB_column) INDEXTYPE IS CTXSYS.CONTEXT;

BEGIN

  CTX_DLL.OPTIMIZE_INDEX('IDX_COLUMN_TXT','FAST');

END;

This didn't mention the ctxsys (https://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm#BEIIEAFD)

I have created similar queries using LIKE and REGEXP_INSTR and need to create the same using CONTAINS so I can make sure I run the one that works the quickest.

SELECT * from table WHERE CLOB_column LIKE '%J.A.V.A.%';  --4.441 seconds



SELECT * from table WHERE REGEXP_INSTR(CLOB_column, '(J.A.V.A.)')>0; --23.528 seconds

oracle indexing escaping contains

asked Nov 26 '18 at 19:22

Jennifer Crosby

235

asked Nov 26 '18 at 19:22

Jennifer Crosby

235

asked Nov 26 '18 at 19:22

Jennifer Crosby

235

asked Nov 26 '18 at 19:22

Jennifer Crosby

235

asked Nov 26 '18 at 19:22

Jennifer Crosby

235

Hi. Have a look at this answer: stackoverflow.com/a/37886431/10300113 . The whole answer is useful to you and it also contains a link to chapter 'punctuations' in 'Oracle Text Indexing Elements': docs.oracle.com/cd/E11882_01/text.112/e24436/…

– Peter
Nov 26 '18 at 19:39

Possible duplicate of Query with wildcard and dot not matching data with Oracle Text index

– Peter
Nov 26 '18 at 19:42

Great thread @Peter! I wonder why I didn't find it? Probably not using correct words in search. Thanks! I'll check this out and let you guys know if it works.

– Jennifer Crosby
Nov 28 '18 at 16:34

add a comment |

Hi. Have a look at this answer: stackoverflow.com/a/37886431/10300113 . The whole answer is useful to you and it also contains a link to chapter 'punctuations' in 'Oracle Text Indexing Elements': docs.oracle.com/cd/E11882_01/text.112/e24436/…

– Peter
Nov 26 '18 at 19:39

Possible duplicate of Query with wildcard and dot not matching data with Oracle Text index

– Peter
Nov 26 '18 at 19:42

Great thread @Peter! I wonder why I didn't find it? Probably not using correct words in search. Thanks! I'll check this out and let you guys know if it works.

– Jennifer Crosby
Nov 28 '18 at 16:34

Hi. Have a look at this answer: stackoverflow.com/a/37886431/10300113 . The whole answer is useful to you and it also contains a link to chapter 'punctuations' in 'Oracle Text Indexing Elements': docs.oracle.com/cd/E11882_01/text.112/e24436/…

– Peter
Nov 26 '18 at 19:39

Possible duplicate of Query with wildcard and dot not matching data with Oracle Text index

– Peter
Nov 26 '18 at 19:42

Great thread @Peter! I wonder why I didn't find it? Probably not using correct words in search. Thanks! I'll check this out and let you guys know if it works.

– Jennifer Crosby
Nov 28 '18 at 16:34

add a comment |

1 Answer
1

active

oldest

votes

Any reason you can't use LIKE?

CREATE TABLE suppliers

( supplier_id number(10) NOT NULL,

  supplier_name varchar2(50) NOT NULL

 );



INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'Apple');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'J.A.V.A');



SELECT * FROM suppliers WHERE supplier_name LIKE '%.%';

SELECT * FROM suppliers WHERE supplier_name LIKE 'J.A.V.A';

answered Nov 26 '18 at 19:47

Dan S

919

No reason, just that I'm supposed to get it working in the most efficient manner and this is the only thing I haven't got working to compare yet. So far on my sample table LIKE is the most efficient, but I would like to see if the CONTAINS is better due to the indexing. It is something that will be run nightly along with other queries, so the quicker the better.

– Jennifer Crosby
Nov 28 '18 at 12:00

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53487720%2foracle-need-to-use-contains-to-find-a-string-that-contains-a-period%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Any reason you can't use LIKE?

CREATE TABLE suppliers

( supplier_id number(10) NOT NULL,

  supplier_name varchar2(50) NOT NULL

 );



INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'Apple');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'J.A.V.A');



SELECT * FROM suppliers WHERE supplier_name LIKE '%.%';

SELECT * FROM suppliers WHERE supplier_name LIKE 'J.A.V.A';

answered Nov 26 '18 at 19:47

Dan S

919

No reason, just that I'm supposed to get it working in the most efficient manner and this is the only thing I haven't got working to compare yet. So far on my sample table LIKE is the most efficient, but I would like to see if the CONTAINS is better due to the indexing. It is something that will be run nightly along with other queries, so the quicker the better.

– Jennifer Crosby
Nov 28 '18 at 12:00

add a comment |

Any reason you can't use LIKE?

CREATE TABLE suppliers

( supplier_id number(10) NOT NULL,

  supplier_name varchar2(50) NOT NULL

 );



INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'Apple');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'J.A.V.A');



SELECT * FROM suppliers WHERE supplier_name LIKE '%.%';

SELECT * FROM suppliers WHERE supplier_name LIKE 'J.A.V.A';

answered Nov 26 '18 at 19:47

Dan S

919

No reason, just that I'm supposed to get it working in the most efficient manner and this is the only thing I haven't got working to compare yet. So far on my sample table LIKE is the most efficient, but I would like to see if the CONTAINS is better due to the indexing. It is something that will be run nightly along with other queries, so the quicker the better.

– Jennifer Crosby
Nov 28 '18 at 12:00

add a comment |

Any reason you can't use LIKE?

CREATE TABLE suppliers

( supplier_id number(10) NOT NULL,

  supplier_name varchar2(50) NOT NULL

 );



INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'Apple');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'J.A.V.A');



SELECT * FROM suppliers WHERE supplier_name LIKE '%.%';

SELECT * FROM suppliers WHERE supplier_name LIKE 'J.A.V.A';

answered Nov 26 '18 at 19:47

Dan S

919

Any reason you can't use LIKE?

CREATE TABLE suppliers

( supplier_id number(10) NOT NULL,

  supplier_name varchar2(50) NOT NULL

 );



INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'Apple');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5000, 'J.A.V.A');



SELECT * FROM suppliers WHERE supplier_name LIKE '%.%';

SELECT * FROM suppliers WHERE supplier_name LIKE 'J.A.V.A';

answered Nov 26 '18 at 19:47

Dan S

919

answered Nov 26 '18 at 19:47

Dan S

919

answered Nov 26 '18 at 19:47

Dan S

919

answered Nov 26 '18 at 19:47

Dan S

919

No reason, just that I'm supposed to get it working in the most efficient manner and this is the only thing I haven't got working to compare yet. So far on my sample table LIKE is the most efficient, but I would like to see if the CONTAINS is better due to the indexing. It is something that will be run nightly along with other queries, so the quicker the better.

– Jennifer Crosby
Nov 28 '18 at 12:00

add a comment |

No reason, just that I'm supposed to get it working in the most efficient manner and this is the only thing I haven't got working to compare yet. So far on my sample table LIKE is the most efficient, but I would like to see if the CONTAINS is better due to the indexing. It is something that will be run nightly along with other queries, so the quicker the better.

– Jennifer Crosby
Nov 28 '18 at 12:00

No reason, just that I'm supposed to get it working in the most efficient manner and this is the only thing I haven't got working to compare yet. So far on my sample table LIKE is the most efficient, but I would like to see if the CONTAINS is better due to the indexing. It is something that will be run nightly along with other queries, so the quicker the better.

– Jennifer Crosby
Nov 28 '18 at 12:00

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ytukyg