| Uncertainty while searching string which contains æøå characters [message #677309] |
Wed, 11 September 2019 04:15  |
 |
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
Hi Team,
Column_value = "560 TRUMPELL LANE æøå"
Above data is residing in particular column of two different tables.
query 1 : select * from address where contains (address_1,:search_criteria)>0
query 2 : Select * from customer_details where contains (complete_address,:search_criteria)>0
I am passing input 314 for both queries , I am able to get data in case of query 1 only.
I am unable to understand why this is happening. My requirement is to search string using only CONTAINS Function.
Could you please suggest on this.
[Updated on: Wed, 11 September 2019 12:41] Report message to a moderator |
|
|
|
| Re: Uncertainty while searching string which contains æøå characters [message #677343 is a reply to message #677309] |
Thu, 12 September 2019 10:49  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You should not get results from either query with only the minimal data and search string that you have provided. You need to provide a more complete example, as I have done below, including a copy and paste of your results. You should check to see that your indexes are current and check whether either is a multi-column index which may be searching other columns that you are not aware of. If you just use select * with your queries, you may see that your 314 is in another column.
-- test environment:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
5 rows selected.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE address (address_1 VARCHAR2(30))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO address (address_1) VALUES ('560 TRUMPELL LANE æøå')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO address (address_1) VALUES ('314 SOMEWHERE ST æøå')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> CREATE INDEX address_idx ON address (address_1) INDEXTYPE IS CTXSYS.CONTEXT
2 /
Index created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE customer_details (complete_address VARCHAR2(30))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO customer_details (complete_address) VALUES ('560 TRUMPELL LANE æøå')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO customer_details (complete_address) VALUES ('314 SOMEWHERE ST æøå')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> CREATE INDEX customer_details_idx ON customer_details (complete_address) INDEXTYPE IS CTXSYS.CONTEXT
2 /
Index created.
-- what is tokenized and indexed:
SCOTT@orcl_12.1.0.2.0> SELECT token_text FROM dr$address_idx$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
314
560
LANE
SOMEWHERE
ST
TRUMPELL
Ã
7 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT token_text FROM dr$customer_details_idx$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
314
560
LANE
SOMEWHERE
ST
TRUMPELL
Ã
7 rows selected.
-- query tests:
SCOTT@orcl_12.1.0.2.0> VARIABLE search_criteria VARCHAR2(30)
SCOTT@orcl_12.1.0.2.0> EXEC :search_criteria := '314'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select * from address where contains (address_1,:search_criteria)>0
2 /
ADDRESS_1
------------------------------
314 SOMEWHERE ST æøå
1 row selected.
SCOTT@orcl_12.1.0.2.0> Select * from customer_details where contains (complete_address,:search_criteria)>0
2 /
COMPLETE_ADDRESS
------------------------------
314 SOMEWHERE ST æøå
1 row selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
