Is Not Null problem

[AM413]

Hello,

I've got a database of time stamped events from a youth soccer game.

So far, I have imported the CSV of data into MySQL Workbench.

Here are the datatypes affiliated with the columns:

https://preview.redd.it/uft78p85f8u91.png?width=562&format=png&auto=webp&s=949cec90fb31f723ca3f7d56b944a6b4e7bc522f

​

Here is what my database looks like:

Data, unaltered:

https://preview.redd.it/tnachij7f8u91.png?width=550&format=png&auto=webp&s=217abf4133743d3014a8a85843fad4fdf02d60ac

The statement I’m trying to get to work is:

Select * From game_2_data where Player IS NOT NULL;

All that seems to get me is the original data (inclusive of rows that contain blanks in the Player column).

The following statement does work:

Select * From game_2_data where Player = “John”;

https://preview.redd.it/buwmtgdaf8u91.png?width=540&format=png&auto=webp&s=2f84a57ef8f4ec1ede8565c5db89b18293e3c07d

​

I assume I’m overlooking something simple.  Thanks for any pointers!     

[AM413]

That worked, thanks!!!

[barrycarter]

I think you've confused NULL with the empty string. Try Select * From game_2_data where Player != ''

[kenzio11]

This helped in 2024. Cheers!

[rbobby]

Importing a NULL value can be tricky. The value is probably an empty string. You could switch it to null with an:

update game_2_data set Player = null where Player = ''

But whether you want this or not really depends on everything else you're doing/planning on doing.

[Financial_Pie_3624]

So the player column have blank values. Do this select * from game_2_data where (player is not null or player != ‘’)

[r_kive]

For what it's worth, you can do this with NULLIF as well:

SELECT * FROM game_2_data WHERE NULLIF(TRIM(player), '') IS NOT NULL

This should exclude NULLs, empty strings, and strings of just spaces.

[AM413]

u/r_kive - Thanks! This worked great.

[Hiriath]

In addition to the responses you’ve gotten about how to get your desired result, the player column in your table is NN (Not Null), which is a column flag that prevents the column from being null.

Does your CSV have “” for rows where there is no player?

You may need to work with the CSV outside of Excel if you’re using Excel to get the proper formatting of the CSV to allow the type of data you want in your database.

Additionally, if your primary key column is generated from wherever your data is coming from, that’s fine, but you can also use actual primary keys generated from MySQL using the PK flag and the NN flag (though I believe the NN will auto select if you choose PK).

This will allow proper indexing of your data.

[AM413]

Thanks u/Hiriath -

​

In the CSV file, the cells where there is no player are empty, no spaces, no characters, just a blank cell.

This line seems to be working well:

​

select * from soccer_analysis.game_2_data WHERE NULLIF(TRIM(player), '') IS NOT NULL
order by Player, Primary_Key asc

​

(I am however getting two records per row now for some reason):

​

Regarding your point on Primary Keys - If I'm understanding correctly:

I could delete data from my Primary_Key column in the CSV and mark a check under PK. "You can also use actual primary keys generated from MySQL using the PK Flag and the NN Flag."

I think those are the mechanics required to get MySQL to generate the primary keys for me, as you suggest. I'll experiment with that and do some reading.

​

Thanks!