When using the SQL Function UPPPER or LOWER to change the case of text it is important to note that any accented characters will lose their accent. This means that text like hélène will become HELENE instead of HÉLÈNE.

If preserving the accented characters is a requirement then using the native 4D commands Uppercase and Lowercase (using the * parameter) would be better suited. If necessary, these commands could even be used within the SQL code by using a simple wrapper along with the available through SQL method property.

For example, let's take the following record data:

Field	ID	Name
Data	1	hélène

Here is some SQL code that gets the Name from Record ID 1 and formats the output in UPPERCASE:

C_TEXT(out) Begin SQL    select UPPER(Name)    from Table_1    where ID = 1    into :out; End SQL ALERT(out)

The code above woud lose any accented characters and output the following:


In order to preserve the accents we could use the native 4D commands Uppercase and Lowercase and leverage the * parameter to preserve accented characters. This can be done either after the query outside of SQL or during the query via a SQL Function call. Let's explore these two options:

After the query:

C_TEXT(out) Begin SQL    select Name    from Table_1    where ID = 1    into :out; End SQL ALERT(Uppercase(out;*))

This produces the following:

---

During the Query (with a SQL FN call):

C_TEXT(out) Begin SQL    select {fn sql_UPPER(Name) as TEXT}    from Table_1    where ID = 1    into :out; End SQL ALERT(out)

This produces the following:


* Note 1: the code above uses a 4D Method named sql_UPPER called via the FN keyword via SQL. Here is the code from the sql_UPPER method:

// sql_UPPER C_TEXT($1;$0) $0:=Uppercase($1;*)

* Note 2: the sql_UPPER method must be set to be available via the method properties: