0

I have the following SQL statement which returns a single record as expected:

select * from geodatasource_cities C,
geodatasource_countries D
where C.CC_FIPS = D.CC_FIPS
and D.CC_ISO='AU'
and UCASE(TRIM(C.FULL_NAME_ND)) LIKE '%JAN JUE%';

However, If I use the following SQL statement, no records are returned. I have only changed the LIKE clause to an equal to clause:

select * from geodatasource_cities C,
geodatasource_countries D
where C.CC_FIPS = D.CC_FIPS
and D.CC_ISO='AU'
and UCASE(TRIM(C.FULL_NAME_ND)) = 'JAN JUE';

Can anybody please help me understand why this may be happening?

Similarly, with the following statement:

select LENGTH(COUNTRY_NAME),
LENGTH('Australia'),
COUNTRY_NAME
from countries
WHERE UCASE(TRIM(COUNTRY_NAME)) LIKE '%AUSTRALIA%'

It returns:

10 | 9 | Australia

EDIT

Here is a sample of the SQL I used to import the data:

load data local infile 'CITIES.TXT'
into table geodatasource_cities
fields terminated by '\t'
lines terminated by '\n'
(CC_FIPS,FULL_NAME_ND);

It appears as though the \n is wreaking havoc on my data. I will try to import with alternative options tomorrow.

Matt G
  • 1,332
  • 2
  • 13
  • 25

1 Answers1

1

In the second case, your requiring an exact match in the database, while in the first case, the name must contain the string "JAN JUE". Is the full name exactly "JAN JUE" in the database? You may also need to check your casing. I know for MS-SQL you can specify your preferred treatment of casing for comparisons. One selection may be case sensitive while the other is not.

Notes about that for the like clause in MySql: http://www.mysqltutorial.org/sql-like-mysql.aspx

EDIT

The MySql Trim() only removes spaces. So you could have a line break. (It could also be non breaking spaces, you may need to test to see if trim removes those also, can't find a definitive answer on that.) Since your getting a different length on fields of one more, I'd suggest checking to make sure your data doesn't have trailing hidden characters like that.

This problem is talked about in this Stack Overflow Question: Does the MySQL TRIM function not trim line breaks or carriage returns?

Community
  • 1
  • 1
ICodeForCoffee
  • 3,187
  • 2
  • 30
  • 40
  • I initially thought it was a case issue, that's why i converted the field FULL_NAME_ND to upper case so I can compare in the same case. – Matt G May 11 '10 at 15:29
  • CRUDE! Yes with how you've written that, it shouldn't be an issue. The text for FULL_NAME_ND is just "Jan Jue" right? – ICodeForCoffee May 11 '10 at 15:32
  • Oh, by the way, "Jan Jue" is in the database. It should actually be, "Jan Juc", which is how I found a problem with the data in the first place, when I went to fix the issue. – Matt G May 11 '10 at 15:36
  • Seems like this may answer the question, I will confirm tomorrow! Thank you! – Matt G May 11 '10 at 16:10
  • Totally imported the data with incorrect parameters, I truncated the table and re-imported with the following sql: LOAD DATA LOCAL INFILE 'CITIES.TXT' INTO TABLE cities CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; – Matt G May 11 '10 at 21:52