Issue with lower SQL function in GridDB

Question

I'm using the SQL statement below to convert the first character of a string column stored in a GridDB collection container to lowercase. I'm using the lower function. However, when the first letter is a character with an accent, the lower function won´t convert it, thus returning the original character.

Select 
    Customer_Name, 
    substr(Customer_Name, 1, 1) as "First Letter", 
    lower (substr(Customer_Name, 1, 1)) as  "First Letter in Lowercase"
From 
    Customer

Any ideas on how to accomplish that?

Please ask 1 specific researched non-duplicate question. Either ask re 1 bad query/function with obligatory minimal reproducible example, including why you think it should return something else at the 1st subexpression where you don't get what you expect, justified by reference to authoritative documentation, or ask about your overall goal, giving working parts you can do, with justification & a minimal reproducible example--then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. How to Ask Help center Basic questions are faqs. — philipxy
– philipxy, Commented Aug 20 at 21:28
This post does not contain any clear precise phrasing of a question by which anyone could search for or find it. tour — philipxy
– philipxy, Commented Aug 20 at 21:33
A different collation might help. (If griddb supports those.) — jarlh
– jarlh, Commented Aug 21 at 6:45

marc_s · Accepted Answer · 2025-08-20 20:15:49Z

1

I was able to correctly convert diacritic characters to their lowercase counterparts by creating the following auxiliary table with two columns and inserting pairs of diacritic characters in lower and upper case.

CREATE TABLE Latin1Accents 
(
    UCASE STRING,
    LCASE STRING
);

INSERT INTO Latin1Accents (UCASE, LCASE) 
VALUES ('À', 'à'), ('Á', 'á'), ('Â', 'â'),
       ('Ã', 'ã'), ('Ä', 'ä'), ('Å', 'å'),
       ('Ç', 'ç'),
       ('È', 'è'), ('É', 'é'), ('Ê', 'ê'), ('Ë', 'ë'),
       ('Ì', 'ì'), ('Í', 'í'), ('Î', 'î'), ('Ï', 'ï'),
       ('Ñ', 'ñ'),
       ('Ò', 'ò'), ('Ó', 'ó'), ('Ô', 'ô'), 
       ('Õ', 'õ'), ('Ö', 'ö'),
       ('Ù', 'ù'), ('Ú', 'ú'), ('Û', 'û'), ('Ü', 'ü'),
       ('Ý', 'ý');

A CASE is used to verify if the UNICODE representation of the first character in the string is above 127, in order to find out if the character could be a diacritic one. If not, the lower function is used to convert the character. However, if the UNICODE value is above 127, a subquery is used to look for the lowercase representation of that character in the Latin1Accents auxiliary table. If the lowercase character could not be found in that table, the original character is returned.

SELECT 
    Customer_Name,
    SUBSTR (Customer_Name, 1, 1) as "First Letter", 
    UNICODE (SUBSTR (Customer_Name, 1, 1)),
    CASE
        WHEN UNICODE (SUBSTR (Customer_Name,1,1)) > 127 
            THEN (SELECT 
                      CASE  
                          WHEN LCASE IS NULL 
                              THEN SUBSTR (Customer_Name, 1, 1) 
                              ELSE LCASE 
                      END
                  FROM  
                      Latin1Accents 
                  WHERE 
                      UCASE = SUBSTR (Customer_Name, 1, 1))
        ELSE
            LOWER (SUBSTR (Customer_Name,1,1))
    END AS "First Letter in Lowercase"
FROM 
    Customer

edited Aug 20 at 20:15

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 20 at 19:00

Danilo Silva

1015 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue with lower SQL function in GridDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related