How can I retrieve an Oracle record when my key field contains a mu ('μ') character?

Question

I have a small table of measurement units in Oracle (10.2.0.4). It's defined as

CREATE TABLE Units (
    UNIT_ID number,
    UNIT varchar2(12)
)

It's populated with a few records, and one of those records has a unit value of 'μL'. When I try to query for that record using this query...

select * from units where unit = 'μL'

.. I get an empty result. I tried using straight SQL inside SQL Developer and also with ODBC parameters, and both cases give me nothing. I can successfully retrieve any of the units that have no such mu character, however. So, how can I successfully retrieve these records? Is it some Oracle character-encoding issue that I am unaware of?

Answer 1

Make sure your connection has the same encoding set like the database table/column. Which encoding does your source code have? If you use java or a .net language it is likely to be utf8 which will be fine.

Answer 2

It is very to be an encoding issue and the encoding used by whatever inserted this character into the table is different from the encoding being used by your query.

You can use the ASCII functions to check that both encodings are the same:

SELECT ASCII(SUBSTR(unit, 1, 1)) FROM units;

This will show the code of the first character in each unit.

Then you can check the encoding in the environment you're running the query from:

SELECT ASCII('µ') FROM dual;

If they're not the same, there's your problem.

Answer 3

Another explanation comes from the fact that Unicode has 2 code points for mu: u00b5 (which is intended to be used as the abbreviation for "micro" in units as in your case) and u03bc (which is intended for use in Greek language text). In most fonts, they are identical in appearance.