0

Env:

Oracle version: Release 10.2.0.1.0 Server: Windows XP

Language in oracle:

select userenv('language') from dual;

Result:
CHINESE_CHINA.AL32UTF8

Table structure:

PARTY_ID NVARCHAR2(50)
PARTY_TYPE NVARCHAR2(50)

Data in the table:

PARTY_ID | PARTY_TYPE
---------+-----------
BEST     | C    
WILL     | C

SQL1:

SELECT  * FROM cc_party  
WHERE party_type = 'C'

Result:

nothing

SQL1:

SELECT  * FROM cc_party  
WHERE party_type = 'C' or party_type = 'C'

Result:

PARTY_ID | PARTY_TYPE
---------+-----------
BEST     | C    
WILL     | C

Question:

Why I can not select data by party_type = 'C' (SQL1) ? Is there some special things about NVARCHAR2 ?

If I update the where clause to party_type = 'C' or party_type = 'C' (SQL2), I can get the result. It makes me confused. I want to know why the result come out when I added or clause.

APC
  • 144,005
  • 19
  • 170
  • 281
Sailing
  • 337
  • 1
  • 2
  • 9
  • 1
    Are those 'C's the same or different 'C's? – Thomas Mueller Sep 19 '10 at 07:45
  • 1
    Also, why did you make this Community Wiki? It's not teh sort of question which fits CW. – APC Sep 19 '10 at 07:55
  • 1
    SELECT DUMP(party_type) FROM cc_party will show you the internal structure of the string. My guess is that the column has an 'exotic' character that looks like a 'C'. But you can also get odd effects with function-based indexes. – Gary Myers Sep 19 '10 at 23:14
  • Query: select dump(party_type), dump('C') from party where trim(party_type) = 'C' Result: Typ=1 Len=2: 0,67 | Typ=96 Len=1: 67 – Sailing Sep 20 '10 at 07:40
  • It seems that the data has space in it. – Sailing Sep 20 '10 at 07:41

1 Answers1

3

Try,and tell me the result

SELECT * FROM cc_party WHERE trim(party_type) = 'C'

kupa
  • 1,861
  • 5
  • 25
  • 41