CODEWITHSUNDEEP
sqloracle-database
nurgan
nurgan

Reputation: 329

Query table with german Umlaut in column name

I have a table which contains a column that has a German umlaut in its name. Yes, I know this is an absolutely bad idea, but I did not create the table and can definitely not alter it, so I have to somehow deal with it.

My problem is that my queries don't seem to work when I use this column in the WHERE clause.

When I execute the following query:

SELECT t.BadUmlautColumnÄ5
FROM myTable t;

everything works fine.

But when I execute this query:

SELECT t.BadUmlautColumnÄ5
FROM myTable t
WHERE t.BadUmlautColumnÄ5 = '20.02.2014'

I simply get an empty result set. No Errors, but just no results.

I also tried:

 WHERE t."BADUMLAUTCOLUMNÄ5" = '20.02.2014'

But its exactly the same.

And I know that there are entries for the value I use in the where clause, because with the query without where clause that works, I can see that there are some.

Google wasnt really helpful on this Topic, so does anyone know how to solve this issue?

The Column with the Umlaut is of type date, if this is somehow relevant. And I am on a Oracle 10g database.

Upvotes: 2

Views: 2753

Answers (3)

Alexander
Alexander

Reputation: 3179

Try that 

SELECT t.BadUmlautColumnÄ5
FROM myTable t
WHERE t.BadUmlautColumnÄ5 = TO_DATE('20.02.2014', 'dd.mm.yyyy');

If wouldn't work - 

SELECT t.BadUmlautColumnÄ5
FROM myTable t
WHERE t.BadUmlautColumnÄ5 = TO_TIMESTAMP ('20.02.2014', 'dd.mm.yyyy');

You can also try 

SELECT t.BadUmlautColumnÄ5
FROM myTable t
WHERE to_char(t.BadUmlautColumnÄ5, 'dd.mm.yyyy') = '20.02.2014';

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191455

It's nothing to do with the column name. it looks like the values in the column have a time component. You can query for the whole day with:

WHERE TRUNC(t.BadUmlautColumnÄ5) = TO_DATE('20.02.2014', 'DD.MM.YYYY')

But if the column is indexed it would be better to do it the other way around:

SELECT TO_CHAR(t.BadUmlautColumnÄ5, 'DD.MM.YYYY HH24:MI:SS')
FROM myTable t
WHERE t.BadUmlautColumnÄ5 >=
    TO_DATE('20.02.2014 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
AND t.BadUmlautColumnÄ5 <=
    TO_DATE('20.02.2014 23:59:59', 'DD.MM.YYYY HH24:MI:SS')

Assuming it is a DATE, not a TIMESTAMP. I've specified the display format, which you should always do anyway, to include the time so you can what is actually in there. It looks like your NLS_DATE_FORMAT is 'DD.MM.YYYY' so it will only show the date part by default. Never rely on implicit date conversion, though.

Upvotes: 3

smnbbrv
smnbbrv

Reputation: 24581

try to use this

SELECT t.BadUmlautColumnÄ5
FROM myTable t
WHERE t.BADUMLAUTCOLUMNÄ5 = date '2014-02-20';

I think you simply have a problem of implicit data convercions.

Upvotes: 0

Related Questions