SQLite query case sensitive

0
votes

I have a table with few columns in SQLite. I have two columns that I need to use for querying this table (code, Description). Both of these have the COLLATE NOCASE when creating the tables as below:

CREATE TABLE [AuditEvent] (
    "Code"      char(4) NOT NULL COLLATE NOCASE,
    "Description"       nvarchar(255) NOT NULL COLLATE NOCASE,
    "AuditEventPK"      guid NOT NULL,
    PRIMARY KEY ([Code])
);

When I query the table using the code I get no results 

select * from auditevent where code = 'add'    -- does not return any value

select * from auditevent where description = 'add' -- returns the right record.

However when I query the table using the description colomun, I get the results.

In some cases, I have to use the CODE, but it is not returning anything. Any idea??

1
sqlsqlitemonoxamarin.ios
Code is set to char(4) So it would be 'add ' with a trailing space. – Silvermind
Thanks Silvermind, that worked, but does that mean whenever I have char(x) I need to tailer it with spaces ?? – Has AlTaiar
Yes, because a varchar is as it's name already implies is variable in length, while char is not. char is always a fixed size. The value will be padded at the right side with spaces if you did not provide enough characters. – Silvermind
Thanks heaps mate, did not know about that and could find any mention to it.. Thanks – Has AlTaiar

1 Answers

2
votes

It appeared that I need to make the CODE column of type varchar instead of char as Silvermind explained. Thanks Silvermind.