select is returning something weird?

[iamnothyper]

hi, my query is:

 

SELECT close FROM stocks WHERE the_date = '2013-04-10' AND ticker = 'JCP';

 

i get an empty set. i have since realized the problem lies with the ticker field because using just the date is fine.

 

so i did

SELECT * FROM stocks;
+----------+------------+--------+--------+--------+--------+-----------+-----------+--------+
| stock_ID | the_date | open | high | low | close | volume | adj_close | ticker |
+----------+------------+--------+--------+--------+--------+-----------+-----------+--------+
| 1 | 2013-04-26 | 409.81 | 418.77 | 408.25 | 417.20 | 27289200 | 417.20 | AAPL
| 2 | 2013-04-25 | 411.23 | 413.94 | 407.00 | 408.38 | 13713700 | 408.38 | AAPL
| 3 | 2013-04-24 | 393.54 | 415.25 | 392.50 | 405.46 | 34630400 | 405.46 | AAPL
| 4 | 2013-04-23 | 403.99 | 408.38 | 398.81 | 406.13 | 23294100 | 406.13 | AAPL
| 5 | 2013-04-22 | 392.64 | 402.20 | 391.27 | 398.67 | 15340900 | 398.67 | AAPL
| 6 | 2013-04-19 | 387.97 | 399.60 | 385.10 | 390.53 | 21749700 | 390.53 | AAPL
| 7 | 2013-04-18 | 404.99 | 405.79 | 389.74 | 392.05 | 23783300 | 392.05 | AAPL
| 8 | 2013-04-17 | 420.27 | 420.60 | 398.11 | 402.80 | 33735300 | 402.80 | AAPL
| 9 | 2013-04-16 | 421.57 | 426.61 | 420.57 | 426.24 | 10890600 | 426.24 | AAPL
| 10 | 2013-04-15 | 427.00 | 427.89 | 419.55 | 419.85 | 11318300 | 419.85 | AAPL
| 11 | 2013-04-12 | 434.15 | 434.15 | 429.09 | 429.80 | 8521900 | 429.80 | AAPL
| 12 | 2013-04-11 | 433.72 | 437.99 | 431.20 | 434.33 | 11727300 | 434.33 | AAPL
| 13 | 2013-04-10 | 428.10 | 437.06 | 426.01 | 435.69 | 13415800 | 435.69 | AAPL
| 14 | 2013-04-26 | 8.35 | 8.36 | 8.26 | 8.27 | 15141900 | 8.27 | AA
| 15 | 2013-04-25 | 8.42 | 8.50 | 8.34 | 8.39 | 18396000 | 8.39 | AA
| 16 | 2013-04-24 | 8.12 | 8.45 | 8.12 | 8.41 | 22031100 | 8.41 | AA
| 17 | 2013-04-23 | 8.12 | 8.17 | 8.04 | 8.12 | 16255700 | 8.12 | AA
| 18 | 2013-04-22 | 8.12 | 8.15 | 7.99 | 8.11 | 14758200 | 8.11 | AA
| 19 | 2013-04-19 | 8.09 | 8.11 | 8.00 | 8.08 | 15661700 | 8.08 | AA
| 20 | 2013-04-18 | 8.00 | 8.08 | 7.90 | 8.03 | 19079900 | 8.03 | AA
| 21 | 2013-04-17 | 8.04 | 8.08 | 7.93 | 7.96 | 27240400 | 7.96 | AA
| 22 | 2013-04-16 | 8.08 | 8.20 | 8.06 | 8.10 | 22820600 | 8.10 | AA
| 23 | 2013-04-15 | 8.14 | 8.17 | 8.01 | 8.04 | 32459300 | 8.04 | AA
| 24 | 2013-04-12 | 8.30 | 8.32 | 8.17 | 8.22 | 21450400 | 8.22 | AA
| 25 | 2013-04-11 | 8.30 | 8.40 | 8.27 | 8.32 | 13180400 | 8.32 | AA
| 26 | 2013-04-10 | 8.43 | 8.47 | 8.30 | 8.31 | 20827200 | 8.31 | AA
| 27 | 2013-04-26 | 31.90 | 31.98 | 31.45 | 31.79 | 47799300 | 31.79 | MSFT
| 28 | 2013-04-25 | 31.71 | 32.84 | 31.54 | 31.94 | 110688300 | 31.94 | MSFT
| 29 | 2013-04-24 | 30.62 | 31.92 | 30.60 | 31.76 | 90946600 | 31.76 | MSFT
| 30 | 2013-04-23 | 30.70 | 30.90 | 30.38 | 30.60 | 59082400 | 30.60 | MSFT
| 31 | 2013-04-22 | 30.30 | 31.18 | 30.27 | 30.83 | 137904000 | 30.83 | MSFT
| 32 | 2013-04-19 | 29.62 | 30.24 | 29.61 | 29.77 | 99790700 | 29.77 | MSFT
| 33 | 2013-04-18 | 28.95 | 28.98 | 28.50 | 28.79 | 56841500 | 28.79 | MSFT
| 34 | 2013-04-17 | 28.85 | 29.04 | 28.60 | 28.83 | 52840700 | 28.83 | MSFT
| 35 | 2013-04-16 | 28.90 | 29.14 | 28.70 | 28.97 | 52797300 | 28.97 | MSFT
| 36 | 2013-04-15 | 28.65 | 28.98 | 28.51 | 28.69 | 56332900 | 28.69 | MSFT
| 37 | 2013-04-12 | 28.85 | 29.02 | 28.66 | 28.79 | 62886300 | 28.79 | MSFT
| 38 | 2013-04-11 | 29.10 | 29.20 | 28.73 | 28.94 | 130907100 | 28.94 | MSFT
| 39 | 2013-04-10 | 29.57 | 30.32 | 29.52 | 30.28 | 71058300 | 30.28 | MSFT
| 40 | 2013-04-26 | 21.39 | 21.53 | 21.25 | 21.40 | 17348300 | 21.40 | MS
| 41 | 2013-04-25 | 21.56 | 21.81 | 21.39 | 21.43 | 18496600 | 21.38 | MS
| 42 | 2013-04-24 | 21.66 | 21.78 | 21.40 | 21.45 | 17955700 | 21.40 | MS
| 43 | 2013-04-23 | 20.97 | 21.73 | 20.91 | 21.61 | 23437700 | 21.56 | MS
| 44 | 2013-04-22 | 20.61 | 20.88 | 20.53 | 20.71 | 16156600 | 20.66 | MS
| 45 | 2013-04-19 | 20.37 | 20.63 | 20.16 | 20.58 | 24644100 | 20.53 | MS
| 46 | 2013-04-18 | 21.35 | 21.47 | 20.31 | 20.31 | 47193400 | 20.26 | MS
| 47 | 2013-04-17 | 21.54 | 21.83 | 20.86 | 21.47 | 45475800 | 21.42 | MS
| 48 | 2013-04-16 | 21.81 | 21.86 | 21.19 | 21.85 | 21839800 | 21.80 | MS
| 49 | 2013-04-15 | 21.75 | 22.25 | 21.41 | 21.48 | 22458100 | 21.43 | MS
| 50 | 2013-04-12 | 22.09 | 22.23 | 21.54 | 21.82 | 25150400 | 21.77 | MS
| 51 | 2013-04-11 | 22.28 | 22.63 | 22.24 | 22.27 | 14692800 | 22.22 | MS
| 52 | 2013-04-10 | 21.89 | 22.53 | 21.87 | 22.22 | 17467300 | 22.17 | MS
| 53 | 2013-04-26 | 16.15 | 17.58 | 15.86 | 17.00 | 56658800 | 17.00 | JCP
| 54 | 2013-04-25 | 15.26 | 15.56 | 15.20 | 15.24 | 10840900 | 15.24 | JCP
| 55 | 2013-04-24 | 15.48 | 15.60 | 15.15 | 15.19 | 7242100 | 15.19 | JCP
| 56 | 2013-04-23 | 15.19 | 15.73 | 14.95 | 15.45 | 14552800 | 15.45 | JCP
| 57 | 2013-04-22 | 15.36 | 15.80 | 15.31 | 15.54 | 13453300 | 15.54 | JCP
| 58 | 2013-04-19 | 15.20 | 15.39 | 14.85 | 15.26 | 17094800 | 15.26 | JCP
| 59 | 2013-04-18 | 14.97 | 15.29 | 14.83 | 15.16 | 29205100 | 15.16 | JCP
| 60 | 2013-04-17 | 15.07 | 15.13 | 14.58 | 14.77 | 17433900 | 14.77 | JCP
| 61 | 2013-04-16 | 14.59 | 15.42 | 14.45 | 15.19 | 28576300 | 15.19 | JCP
| 62 | 2013-04-15 | 14.43 | 15.15 | 14.27 | 14.39 | 32590400 | 14.39 | JCP
| 63 | 2013-04-12 | 15.01 | 15.03 | 14.24 | 14.62 | 36423900 | 14.62 | JCP
| 64 | 2013-04-11 | 14.19 | 15.12 | 14.16 | 14.86 | 27459000 | 14.86 | JCP
| 65 | 2013-04-10 | 14.03 | 14.36 | 13.55 | 14.09 | 34677000 | 14.09 | JCP
+----------+------------+--------+--------+--------+--------+-----------+-----------+--------+

Then I did

 

SELECT ticker FROM stocks;
+--------+
| ticker |
+--------+
|APL
|APL
|APL
|APL
|APL
|APL
|APL
|APL
|APL
|APL
|APL
|APL
|APL
|
|
|
|
|
|
|
|
|
|
|
|
|
|SFT
|SFT
|SFT
|SFT
|SFT
|SFT
|SFT
|SFT
|SFT
|SFT
|SFT
|SFT
|SFT
|
|
|
|
|
|
|
|
|
|
|
|
|
|P
|P
|P
|P
|P
|P
|P
|P
|P
|P
|P
|P
|P
+--------+

can someone please tell me what the hell is going on with the ticker column? and, this is imported from a csv file, if that means anything.

[mac_gyver]

when you were importing the data, did you trim the values, because the last field in each line probably has a new-line character on it.

[iamnothyper]

mac_gyver, on 04 May 2013 - 8:06 PM, said:

when you were importing the data, did you trim the values, because the last field in each line probably has a new-line character on it.

i used this to load it:

LOAD DATA LOCAL INFILE "stock_data.csv" INTO TABLE stocks FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

 

and i just did this to check:

SELECT close FROM stocks WHERE ticker = "JCP\n";

 

to test that and i still got an empty set?

[iamnothyper]

i used this to load it:

LOAD DATA LOCAL INFILE "stock_data.csv" INTO TABLE stocks FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

 

and i just did this to check:

SELECT close FROM stocks WHERE ticker = "JCP\n";

 

to test that and i still got an empty set?

i also just did UPDATE stocks SET ticker = trim(ticker); in case but i get the same result?

[DavidAM]

It may be a Carriage Return ("\r") on the end of the field.

[iamnothyper]

DavidAM, on 04 May 2013 - 8:30 PM, said:

It may be a Carriage Return ("\r") on the end of the field.

i tried the select with that to test it but

still an empty set D;

what's wrong with thisss

 

and the select result is still the same

Edited May 5, 2013 by iamnothyper

[mac_gyver]

i also just did UPDATE stocks SET ticker = trim(ticker); in case but i get the same result?

 

by default, the mysql trim() function only removes spaces. you would need to supply the optional remstr parameter to get it to trim anything else.

[kicken]

Run this:

SELECT HEX(ticker) FROM stocks

That will print out the value as hex pairs which you can use to figure out what extra characters are present.

[iamnothyper]

got it, the problem was the \r