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'.

Answer 1

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