ODBC, SQL and german umlauts 
Author Message
 ODBC, SQL and german umlauts
Hi,

I have some problems with german umlauts (?????) in SQL SELECTs.
Example:
SELECT * FROM customer WHERE name >= 'K' AND name <=
'Kzzzzzzzzzzzzzzzzzzzzzzz'

The table contains a customer 'K?nig'.  The SELECT does not find it.
Sure, the ASCII codes for the umlauts are higher than 'z', but it must
be possible to change the collating sequence.
I've tried some searches in the Knowledge Base (ACS, Collate etc.) but
without success.
Can someone help me?

thanks

Andreas



Mon, 08 Nov 2004 17:31:31 GMT
 ODBC, SQL and german umlauts

Changing the collationg sequence is possible (at least with PSQL2000/SP4),
but VERY uncomfortable. You must specify an ACS file when you create the
table.

The syntax goes something like this:

CREATE TABLE mytable (name CHAR(20) COLLATE 'c:\german.alt')

You can modify your select to work around this problem:

SELECT * FROM customer WHERE name >= 'K' AND name < 'L'
or SELECT * FROM customer WHERE name like 'K%'

Bernhard


Quote:
> Hi,

> I have some problems with german umlauts (?????) in SQL SELECTs.
> Example:
> SELECT * FROM customer WHERE name >= 'K' AND name <=
> 'Kzzzzzzzzzzzzzzzzzzzzzzz'

> The table contains a customer 'K?nig'.  The SELECT does not find it.
> Sure, the ASCII codes for the umlauts are higher than 'z', but it must
> be possible to change the collating sequence.
> I've tried some searches in the Knowledge Base (ACS, Collate etc.) but
> without success.
> Can someone help me?

> thanks

> Andreas



Mon, 08 Nov 2004 23:04:07 GMT
 ODBC, SQL and german umlauts

says...
Quote:
> Changing the collationg sequence is possible (at least with PSQL2000/SP4),
> but VERY uncomfortable. You must specify an ACS file when you create the
> table.

Thanks, but the tables are not created with SQL-CREATE.  How can I later
change the ACS (PSQL2000i/SP4)?

Quote:
> SELECT * FROM customer WHERE name >= 'K' AND name < 'L'
> or SELECT * FROM customer WHERE name like 'K%'

Yes, but our clients wanted the to/from range input.

Andreas



Fri, 12 Nov 2004 13:30:15 GMT
 ODBC, SQL and german umlauts
On Mon, 27 May 2002 07:30:15 +0200, Andreas Niggemann

Quote:


>says...
>> Changing the collationg sequence is possible (at least with PSQL2000/SP4),
>> but VERY uncomfortable. You must specify an ACS file when you create the
>> table.
>Thanks, but the tables are not created with SQL-CREATE.  How can I later
>change the ACS (PSQL2000i/SP4)?

You can't. You have to recreate the table and copy the original data
into it.

Quote:
>> SELECT * FROM customer WHERE name >= 'K' AND name < 'L'

or!

Quote:
>> or SELECT * FROM customer WHERE name like 'K%'
>Yes, but our clients wanted the to/from range input.

Are you implying that the input parameters 'K' and 'Kzzzz' from your
original post are user input parameters? If that's the case, I feel
that Bernards first solution might be easier to understand for the
customer (use a 'less then' instead of 'less or equal'), although I
must admit that it could be hard to comprehend that "K?nig"  will not
be returned when searching for names between "Ko" and "Kp".

Quote:
>Andreas

 Gordon Bos
 Q-RY Solutions
 +31-(0)15-2564035

 http://www.q-ry.nl/



Fri, 12 Nov 2004 17:50:18 GMT
 ODBC, SQL and german umlauts


Quote:
> You can't. You have to recreate the table and copy the original data
> into it.

Ok. Thats the hard way :-)

Quote:
> Are you implying that the input parameters 'K' and 'Kzzzz' from your
> original post are user input parameters?

Yes. User input.  In our program the customer inputs a 'K', presses the
TAB key and the 'K' is copied to the second input field and appended
with 'zzzzzzzzzzzzz'.  So the customer can cover the whole range of
names starting with K.

Quote:
> that Bernards first solution might be easier to understand for the
> customer (use a 'less then' instead of 'less or equal'), although I
> must admit that it could be hard to comprehend that "K?nig"  will not
> be returned when searching for names between "Ko" and "Kp".

The character code for '?' is greater than the code for 'z'.  Thats the
main problem.  Today we use '' as the string to append
since '' has a character code above 'z' and all the other umlauts.  But
'K' looks definitly ugly.

Andreas



Fri, 12 Nov 2004 20:34:47 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. german umlauts in SQL*plus/Windows w/ WE8PC850 charset

2. DTS Import, German Umlaut, ignor some characters

3. german umlaut signs false representation after read from SQL2000 database

4. lower does not handle german umlaut

5. Troubles using German Umlauts with JDBC

6. oracle jdbc and german umlauts

7. oas+jservlets and german umlauts

8. German umlauts

9. WebDB: German umlauts problem

10. German Umlauts and ISQL

11. INFORMIX and german "umlaut"

12. ODBC & Umlauts


 
Powered by phpBB® Forum Software