-1

I have this table:

CREATE TABLE `country` (
  `name` VARCHAR(60) NOT NULL,
  `code` VARCHAR(3) UNIQUE NOT NULL,
  PRIMARY KEY  (`code`)
);

As you can see the primary key of this table is the word code When I try to select a specific code in this table, that is 2 characters long, it cannot find anything.

On the other hand, when I select a 3 characters long code like this: select * from `country` where `code` = "TZA";

I get the result I want

I searched for my variable in the table (for example the code "AL") and it appears to be registered.

enter image description here

Why is this happening and how could I make it work?

Thank you in advance!

I am importing my data from a csv file that looks like this:

enter image description here

LOAD DATA LOCAL INFILE 'path_to_file\\countries.csv'
INTO TABLE `country`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(`name`, `code`);

I have tried selecting with a space in the end of the code and on the front of it:

select * from `country` where `code` = 'AL ';
select * from `country` where `code` = ' AL';

But they output nothing

BrainlessPOMO
  • 69
  • 6
  • Can you reproduce your issue in a fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7? – forpas Feb 19 '22 at 16:51
  • Does not seem to be a problem? https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=6a08b6dbffec9ed90d89402d9295b49b – RiggsFolly Feb 19 '22 at 16:55
  • It is unclear what your REAL problem is. Just saying that `select * from `country` where `code` = "TZA";` produced the result you want, and stating that `"AL"` seems to be registered is not enough. What do you want to do ? – Luuk Feb 19 '22 at 17:02
  • Well. I tried it myself, inputing data with a query and It works. I am gonna change my question because I am inputing data from a csv file – BrainlessPOMO Feb 19 '22 at 17:04
  • Your data probably contains "AL " (a space as third character) and not "AL". you could fix that using `update country set code=trim(code);` – Luuk Feb 19 '22 at 17:05
  • @Luuk I did that update function, but the response was this: 0 row(s) affected Rows matched: 248 Changed: 0 Warnings: 0 As I mention above I have tried both selecting the "AL " and " AL" My ultimate problem is that the column `code` is used in an other table (as a foreign key) in which I cannot add anything that has not a 3 characters long `code` column – BrainlessPOMO Feb 19 '22 at 17:15
  • Try `SELECT code, HEX(code) FROM country WHERE code like '%AL%';`.. The HEX(code) should produce `414C`, but probably you will get `414C0D` (or something else). – Luuk Feb 19 '22 at 17:20
  • @Luuk That worked and the hex value is: 414C0D. It appears it has a space in front of it. How can I get rid of that (trim() did not work)? – BrainlessPOMO Feb 19 '22 at 17:28
  • 1
    `update country set code=replace(code,'\r','');`. this should work because `select hex('\r');` produces `0D`. – Luuk Feb 19 '22 at 17:31
  • By the way, `\r` means it's not a 'space' (as in the space bar). Here, [refer this](https://stackoverflow.com/questions/1279779/what-is-the-difference-between-r-and-n). And refer [this example](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=83aad700240dfc57402edc769d1cfee8) – FanoFN Feb 21 '22 at 02:13

1 Answers1

0

The real solution is:

When importing this CSV file you should use:

LOAD DATA LOCAL INFILE 'path_to_file\\countries.csv'
INTO TABLE `country`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(`name`, `code`);

Because your lines seems to be terminated the way Windows terminates lines.

Luuk
  • 12,245
  • 5
  • 22
  • 33