0

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??

Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
Has AlTaiar
  • 4,052
  • 2
  • 36
  • 37
  • 1
    `Code` is set to `char(4)` So it would be `'add '` with a trailing space. – Silvermind May 01 '13 at 07:02
  • 1
    Thanks Silvermind, that worked, but does that mean whenever I have char(x) I need to tailer it with spaces ?? – Has AlTaiar May 01 '13 at 07:04
  • 1
    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 May 01 '13 at 07:06
  • Thanks heaps mate, did not know about that and could find any mention to it.. Thanks – Has AlTaiar May 01 '13 at 07:08

1 Answers1

2

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

Has AlTaiar
  • 4,052
  • 2
  • 36
  • 37
  • It would be nice if you would add the information to your answer ;) – Silvermind May 01 '13 at 07:13
  • Also, I could not find it too for `SqLite`, but it's behaviour is common. For extra information from Oracle: [CHAR Datatype](http://docs.oracle.com/cd/B10501_01/server.920/a96524/c13datyp.htm#1961) – Silvermind May 01 '13 at 07:18
  • This kind of fixed-length field is very common in Cobol and other software from that era. – Bill Torcaso Mar 07 '21 at 20:39