Home » Server Options » Text & interMedia » Uncertainty while searching string which contains æøå characters (Oracle,12c)
Uncertainty while searching string which contains æøå characters [message #677309] Wed, 11 September 2019 04:15 Go to next message
jgjeetu
Messages: 365
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 Go to previous message
Barbara Boehmer
Messages: 8919
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.


Previous Topic: Stoplists and Nickname lists
Next Topic: how to ignore particular string or character in contains function
Goto Forum:
  


Current Time: Sun Dec 08 09:59:37 CST 2019