Vietnameses Unicode text in SQLite

Question

I know that this question was asked in this site, but all of answers didn't solved the problem. So I ask again my problem here.

I created a sqlite db (via Firefox Sqlite manager tool), and the data is stored with Vietnamese.

CREATE TABLE "customer" ("cus_id" TEXT, "cus_name" TEXT, "cus_address" TEXT, "cus_phone" TEXT);

INSERT INTO "customer" VALUES('KH1','Trần Văn A','Hai Bà Trưng, quận 1','0908112233');
INSERT INTO "customer" VALUES('KH2','Lê Hoài B','Hai Bà Trưng, quận 1','0908112233');
INSERT INTO "customer" VALUES('KH3','Nguyễn Thành C','Hai Bà Trưng, quận 1','0908112233');

And my app is iOS app. User search text "Tran" I tried the query:

SELECT * FROM customer WHERE cus_name LIKE '%Tran%';

I tried another solution:

SELECT * FROM customer WHERE cus_name COLLATE UTF8CI LIKE '%Tran%';

But I got 0 record. And then I have tried more solutions which I found on internet but I got 0 record too.

Please help. Thank you.

Before trying anything, just fire this query and check what you're getting: `SELECT * FROM customer WHERE cus_name LIKE '% Trần%';` – If you're getting result with this query then we can look after for another cases. — Hemang, Oct 29 '15 at 05:14
@hagile: yes of course, I get result if using your sql (I already tried). — Bentley, Oct 29 '15 at 06:41
OK that seems that you've properly setup your database. So now, what you need to do is that user should input exactly same input as into the text. — Hemang, Oct 29 '15 at 06:47
@hagile: The problem is the request which require user inputing "tran" but the app can get record with name "Trần Văn A". — Bentley, Oct 29 '15 at 06:57

tuledev · Answer 1 · 2015-10-30T08:28:08.743

You should create one more field in your database table for the non-unicode Vietnamese value. And you will search in this field.

For removing signs in the Vietnamese string:

#define specialCharacter @[@"áàạảãâấầậẩẫăắằặẳẵ",@"ÁÀẠẢÃÂẤẦẬẨẪĂẮẰẶẲẴ",@"éèẹẻẽêếềệểễ",@"ÉÈẸẺẼÊẾỀỆỂỄ",@"óòọỏõôốồộổỗơớờợởỡ",@"ÓÒỌỎÕÔỐỒỘỔỖƠỚỜỢỞỠ",@"úùụủũưứừựửữ",@"ÚÙỤỦŨƯỨỪỰỬỮ",@"íìịỉĩ",@"ÍÌỊỈĨ",@"đ",@"Đ",@"ýỳỵỷỹ",@"ÝỲỴỶỸ"];
#define replaceCharacter @"aAeEoOuUiIdDyY";

    +(NSString *)removeSign4VietnameseString : (NSString*)str
    {
        NSString *tempStr = str;
        //
        NSArray * _vietNameCharacter = specialCharacter;
        NSString * _replaceCharacter = replaceCharacter;
        for (int i = 0; i<_vietNameCharacter.count; i++) {
            NSString* cRe = [NSString stringWithFormat:@"%C",[_replaceCharacter characterAtIndex:i]];
            for (int j = 0; j<[[_vietNameCharacter objectAtIndex:i] length]; j++) {
                NSString* cOri = [NSString stringWithFormat:@"%C",[[_vietNameCharacter objectAtIndex:i] characterAtIndex:j]];
                tempStr = [tempStr stringByReplacingOccurrencesOfString:cOri withString:cRe];
            }
        }
        return [tempStr lowercaseString];
    }

Vietnameses Unicode text in SQLite

1 Answers1