Ucase and OEM to ANSI, won't work with Swedish åäö

Posted on 2008-11-19
Last Modified: 2013-12-25

I have i Pervasive table that is in DOS-format. Now building a program in VB 6. For a while the tables data need to stay in DOS-format.

1) Auto Translate=True in the connection string fixes the presentation of data.
2) CharToOemBuff converts fine when I'm writing data
3) The fields are not Case Insensitive
4) Did try PSQL 8.x and 9.5
5) Function CharToOem() uses API CharToOemBuff()

My problem is when using search on words with i.e. å, ä och ö in.

This works fine:
 1) SELECT myfield FROM mytable WHERE Ucase(myfield) LIKE '%" & CharToOem("BIKE") & "%'"
 Will find all fileds with Bike, BIKE and bike etc.

This will not work:
 2) SELECT myfield FROM mytable WHERE Ucase(myfield) LIKE '%" & CharToOem("SÖNDAG") & "%'"
 Will find words like SÖNDAG, sÖndag but not söndag.

 3) SELECT myfield FROM mytable WHERE Lcase(myfield) LIKE '%" & CharToOem("söndag") & "%'"
 Will find words like söndag but not SÖNDAG

The problem seams to be that SQL command Lcase() and Ucase() only convert a-z, not åäö etc.

Is my only option to adjust the 10 year old kernel in the dos-code to handle åäö?? That would be a pain...
Question by:lizoft
    LVL 28

    Expert Comment

    by:Bill Bach
    I know this isn't the answer that you're looking for, but with PSQLv9 and higher, you can write yourself a new LCASE or UCASE function to manually convert those special characters.  (See the manual onder "CREATE FUNCTION" for more information.  I am guessing that Pervasive is using the standard "C" string conversion functions, which explains why the built-in functions won't convert those special characters at all.

    If you have an option of running PSQLv10, then I would strongly recommend that you contact Pervasive and open a trouble ticket with them.  This sounds like a bug that could be fixed.  

    You may also wish to investigate the International Sorting Rules (ISR's), which might also be able to handle this.  You'd have to specify a different collating sequence on your file, though, which might confuse the DOS app.

    Author Comment

    Sounds greate with Create Function. Did find info on

    Can you help me with code for making own Ucase that also converts å -> Å, ä -> Ä and ö -> Ö?

    Can't use PSQLv10 as it has no DOS-support.
    LVL 28

    Expert Comment

    by:Bill Bach
    Are you still running on Native DOS machines?  PSQLv10 DOES work with DOS applications running on Win32 platforms (Windows 2000 and above).

    Unfortunately, I have no way to easily test anything that I create (being a US user working with an inferior character set).  As such, I can give you the core of the function, but you'll have to try to fix it and do some testing on your own.  I'll work up something and post it shortly...
    LVL 28

    Expert Comment

    by:Bill Bach
    I wrote the function and actually found that I could cut&paste the characters from your post into the PCC.  When I run a simple query of SELECT First_Name, UCASE(First_Name) FROM Person and include some of the special characters, I *do* get the special characters converted to Upper case:

    First_Name        EXPR_1                                                                                                                                                
    ===============   ===============  
    Jåmes             JÅMES
    Kånagae           KÅNAGAE

    I was also able to issue the following query:
      SELECT First_Name, UCASE(First_Name) FROM Person WHERE UCASE(First_Name) LIKE '%Å%'
    This also returned for me the two same records.  

    I ran these tests on both PSQLv10.13.015 and PSQLv9.52.056, the current versions available from Pervasive.  If you have a test system with an older v9.50.077, then I would urge you to patch it to 9.52.056 from the free download on Pervasive's web site, and re-test.  If it STILL doesn't work, then the problem may be in the string that your application is providing via the OEM conversion, and not in the SQL engine.

    Author Comment

    I run my Dos program on Windows XP in a dos-window, using PSQL v8.x (test customer) and PSQL v9.50.077 (my test enviroment). Will download and patch to 9.52.056 and the try again.

    It works fine for me to when READING data. It is when using WHERE UCASE(First_Name) LIKE '%Å%' it doesn't work. I have checked Use OEM/ANSI Conversion.

    Thanks so far for your help! I will come back with my result.

    ** 2 min later:  **
    Aha, YOUR data is with Windows "å". The Dos-format for "å" (Chr(134)) is   in Windows. In Dos: "Båstad", shows "B stad" in PCC. But with OEM/ANSI Conversion will it work fine in VB as it uses ODBC. What I want is Ucase() to convert " " to "ý". Chr(134) to Chr(143) in ASCII code. That would convert å to Å when OEM/ANSI Conversion is on. The big problem seams to be that my tables data is in Dos-format. The solution (?) is to use CREATE FUNCTION and make an own Ucase() that changes my selected ascii-codes, i.e. 134 to 143, 148 to 153 and some more. I sample code how to do that would help me a lot!

    Author Comment

    Hmm, the characters in my text is translated in the page. But the ascii codes is correct.

    Author Comment

    And here the result as it looks in VB with OEM/ANSI Conversion.
    LVL 28

    Accepted Solution

    Hmm.  I must, then, stand by my original statement that I have no way to test this functionality for you.  However, here's the shell of the application that I wrote.

    You can extend this by changing the characters to exactly what you need.  For more fine-grained control, change the characters inside the quotes to CHAR(134), CHAR(143), etc.

    If you have a problem with the REPLACE function working with the OEM character sets, then another alternative is to build the string one character at a time within the loop, but this will be MUCH slower to execute.

    Please test this & let me know what you get...
    	DECLARE :result VARCHAR(250);
    	-- Convert string to upper case first, which should reduce the workload
    	SET :result = UCASE(:str);
    	-- Next, find any special characters and fix them, too	
    	SET :result = REPLACE(:result,'å','Å');
    	SET :result = REPLACE(:result,'ä','Ä');
    	SET :result = REPLACE(:result,'ö','Ö');
    	RETURN :result;

    Open in new window


    Author Closing Comment

    This did finally work! Needed to change SET :result = REPLACE(:result,'å','Å'); to SET :result = REPLACE(:result,Char(134),Char(143)); as you wrote. The sat thing is that this is really slow. Using it in a Super Seach with some 20 textfields and all of them with LIKE.
    - WHERE myField LIKE 'mytext' = 0,5 sec
    - WHERE Ucase(myField) LIKE 'mytext' = 2,5 sec (built in function that doesnt work well with OEM/ANSI Convertion)
    - WHERE My_Ucase(myField) LIKE 'mytext' = 10 sec (UDT)
    But I did for sure learn something today; It's better to use real Windows tables than to convert Dos tables on the fly.

    Again, thanks a lot BillBach!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now