Skip to main content
Solved

WHERE clause - exclude null values

F

Please can someone tell me how to write a WHERE clause in the feature reader that excludes records that do not contain a value.

  • I have thousands of records and some of them have a Global ID - I want to keep these records.
  • I also have records that contain NULL or no value - I want to exclude these values.

I can perform this function in a Tester, but a colleague informed me having this clause built in to the feature reader instead will be much quicker. Perhaps someone can confirm this.

Thanks, Frank

Best answer by chriswilson

If you're talking SQL (or geodatabase SDE or file geodatabase readers) then try:

WHERE your_column <> ' ' AND your_column IS NOT NULL

 

Seemingly you don't need the AND/onwards part but if you want to be explicit then you can include it.

https://stackoverflow.com/questions/8660203/how-to-check-for-is-not-null-and-is-not-empty-string-in-sql-server.

 

Your colleague is correct that using this will be quicker as it will avoid reading records you don't need into memory.

View original
Did this help you find an answer to your question?

2 replies

chriswilson
Supporter
Forum|alt.badge.img+11
  • chriswilsonSupporter
  • Supporter
  • Best Answer
  • August 24, 2022

If you're talking SQL (or geodatabase SDE or file geodatabase readers) then try:

WHERE your_column <> ' ' AND your_column IS NOT NULL

 

Seemingly you don't need the AND/onwards part but if you want to be explicit then you can include it.

https://stackoverflow.com/questions/8660203/how-to-check-for-is-not-null-and-is-not-empty-string-in-sql-server.

 

Your colleague is correct that using this will be quicker as it will avoid reading records you don't need into memory.

F
chriswilson wrote:

If you're talking SQL (or geodatabase SDE or file geodatabase readers) then try:

WHERE your_column <> ' ' AND your_column IS NOT NULL

 

Seemingly you don't need the AND/onwards part but if you want to be explicit then you can include it.

https://stackoverflow.com/questions/8660203/how-to-check-for-is-not-null-and-is-not-empty-string-in-sql-server.

 

Your colleague is correct that using this will be quicker as it will avoid reading records you don't need into memory.

Thanks Chris. This has done the trick, much appreciated.

Reply


Helpful Members This Week

Community Stats

31,235
Posts
118,439
Replies
38,833
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.