Home » Server Options » Text & interMedia » MultiByte Characters (11.2.0.2.0)
MultiByte Characters [message #586306] Wed, 05 June 2013 03:50 Go to next message
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
Senior Member
Hi All,

I have a varchar2 column, when I try to search with like operator for Multibyte chars(like ☂) am getting results as expected.

But when trying with 'Contains' am not getting the desire results. The column is context indexed.

Is there a way to encode to search multibyte using CONTAINS.


Thanks in advance.
-YJ
Re: MultiByte Characters [message #586376 is a reply to message #586306] Wed, 05 June 2013 14:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following example demonstrates that it works. You need to provide a similar copy and paste of code executed from SQL*Plus that demonstrates what happens on your system. If we can determine that when we run the same code, we get different results, then we can focus on what is different between your system and mine. It may have to do with your database characterset or client characterest.

SCOTT@orcl_11gR2> create table test_tab
  2    (test_col  varchar2(60))
  3  /

Table created.

SCOTT@orcl_11gR2> insert into test_tab values ('☂')
  2  /

1 row created.

SCOTT@orcl_11gR2> insert into test_tab values ('word1 ☂ word2')
  2  /

1 row created.

SCOTT@orcl_11gR2> insert into test_tab values ('other stuff')
  2  /

1 row created.

SCOTT@orcl_11gR2> select * from test_tab
  2  /

TEST_COL
------------------------------------------------------------
☂
word1 ☂ word2
other stuff

3 rows selected.

SCOTT@orcl_11gR2> create index test_idx
  2  on test_tab (test_col)
  3  indextype is ctxsys.context
  4  /

Index created.

SCOTT@orcl_11gR2> select * from test_tab
  2  where  test_col like '%☂%'
  3  /

TEST_COL
------------------------------------------------------------
☂
word1 ☂ word2

2 rows selected.

SCOTT@orcl_11gR2> select * from test_tab
  2  where  contains (test_col, '☂') > 0
  3  /

TEST_COL
------------------------------------------------------------
☂
word1 ☂ word2

2 rows selected.

Re: MultiByte Characters [message #586460 is a reply to message #586376] Thu, 06 June 2013 07:53 Go to previous messageGo to next message
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
Senior Member
Thanks!..

am missing something in my app.. Sad
Re: MultiByte Characters [message #586478 is a reply to message #586460] Thu, 06 June 2013 13:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Perhaps your index has not been synchronized since the insert of the data with the special character. You might try synchronizing and optimizing your index or rebuilding the index or dropping and recreating the index.
Re: MultiByte Characters [message #586656 is a reply to message #586478] Sun, 09 June 2013 01:02 Go to previous message
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
Senior Member
There is job which rebuilds the index every 30 mins.

Acutely I was busy with other issue, couldn't get time look into this.. will come up with little more analysis.

Thanks!
Previous Topic: Does Text indexes impacts on DML activities performance
Next Topic: Improve ctx_ddl.sync_index performance
Goto Forum:
  


Current Time: Thu Mar 28 12:29:58 CDT 2024