Why the WHERE clause in Oracle XE database doesn't work when comparing with a string?

Question

I have a table called MEDECIN with 2 columns as follows :

SQL> DESC MEDECIN;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM_MED                                   NOT NULL NUMBER(4)
 SPECIALITE                                NOT NULL NVARCHAR2(13)

And it contains 32 rows, here is its content :

SQL> SELECT * FROM MEDECIN;

   NUM_MED SPECIALITE
---------- ----------------------------------------------------
         4 Orthopédiste
         7 Cardiologue
         8 Cardiologue
        10 Cardiologue
        19 Traumatologue
        24 Orthopédiste
        26 Orthopédiste
        27 Orthopédiste
        31 Anesthésiste
        34 Pneumologue
        50 Pneumologue
        53 Traumatologue
        54 Pneumologue
        64 Radiologue
        80 Cardiologue
        82 Orthopédiste
        85 Anesthésiste
        88 Cardiologue
        89 Radiologue
        99 Anesthésiste
       113 Pneumologue
       114 Traumatologue
       122 Pneumologue
       126 Radiologue
       135 Anesthésiste
       140 Cardiologue
       141 Traumatologue
       144 Radiologue
       152 Cardiologue
       179 Anesthésiste
       180 Cardiologue
       196 Traumatologue

32 rows selected.

The problem is that when I execute the request SELECT * FROM MEDECIN WHERE SPECIALITE = 'Cardiologue'; I get no rows selected ! How can this happens ? As you can see, there is many rows where SPECIALITE = 'Cardiologue'.

May be you can find out answer something here: http://stackoverflow.com/questions/4401043/oracle-text-will-not-work-with-nvarchar2-what-else-might-be-unavailable — MaxXx1313, Oct 09 '15 at 08:51
Maybe there are some trailing blanks. Does `trim(SPECIALITE) = 'Cardiologue'` work? — , Oct 09 '15 at 08:56
@a_horse_with_no_name thanks, that works, but I don't know why because I have inserted the values from a script and I verified that it contains no trailing or leading white spaces (at least that what was visible to me) — Hamza Abbad, Oct 09 '15 at 13:51

Lalit Kumar B · Accepted Answer · 2015-10-09T09:18:03.073

Should work, unless the filter is failing to match any rows.

Setup

SQL> CREATE TABLE MEDECIN
  2    (
  3      NUM_MED NUMBER(4) NOT NULL,
  4      SPECIALITE NVARCHAR2(13) NOT NULL
  5    );

Table created.

SQL> INSERT INTO MEDECIN VALUES
  2    (4, 'Orthopédiste'
  3    );

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM medecin;

   NUM_MED SPECIALITE
---------- -------------
         4 Orthopédiste

Query

SQL> SELECT * FROM MEDECIN WHERE SPECIALITE = 'Orthopédiste';

   NUM_MED SPECIALITE
---------- -------------
         4 Orthopédiste

You could also try TRIM/LIKE to remove any trailing spaces.

For example,

SQL> INSERT INTO MEDECIN VALUES
  2    (5, 'Orthopédis   '
  3  );

1 row created.


SQL> SELECT * FROM MEDECIN WHERE SPECIALITE = 'Orthopédis';

no rows selected

SQL> SELECT * FROM MEDECIN WHERE SPECIALITE LIKE 'Orthopédis%';

   NUM_MED SPECIALITE
---------- -------------
         4 Orthopédiste
         5 Orthopédis

SQL> SELECT * FROM MEDECIN WHERE TRIM(SPECIALITE) = 'Orthopédiste';

   NUM_MED SPECIALITE
---------- -------------
         4 Orthopédiste

Thanks, `trim` works for me, that's strange because the values was inserted from a script containing no trailing spaces! — Hamza Abbad, Oct 09 '15 at 13:41

score 0 · Answer 2 · answered Oct 09 '15 at 08:52

0

Since it is an nvarchar2 try to put a capital N in front of the string like so = N'Cardiologue'.

answered Oct 09 '15 at 08:52

Ted Johansson

1

score -1 · Answer 3 · answered Oct 09 '15 at 08:50

-1

Try

    SELECT * FROM MEDECIN WHERE to_char(SPECIALITE) = 'Cardiologue'

answered Oct 09 '15 at 08:50

Vance

897
5
9

Why the WHERE clause in Oracle XE database doesn't work when comparing with a string?

3 Answers3