Azure Data Lake Analytics U-SQL过滤空/Null字符串问题求助

Hey there! As a fellow U-SQL user who's stumbled through similar quirks with ADLA, let's break down why your empty string filter works in SELECT but fails in WHERE—especially since it works on other datasets (so syntax isn't the issue).

Common Culprits & Fixes #

1. You're Mixing Up Empty Strings ("") and NULL Values

U-SQL treats empty strings and NULL as distinct values, which is a super common gotcha. If your dataset has NULL values instead of (or alongside) empty strings, a simple WHERE col != "" won't filter out the NULLs—they'll still pass through. But when you use the same logic in a SELECT (like a CASE statement), you might be handling NULLs implicitly without noticing.

Fix: Explicitly check for both NULL and empty strings:

WHERE col IS NOT NULL AND col != ""

Or use the .NET string helper method that handles both in one go:

WHERE !string.IsNullOrEmpty(col)

2. "Empty" Values Are Actually Whitespace (Not True Empty Strings)

Sometimes what looks like an empty string is actually spaces, tabs, or line breaks. These won't match col == "", but they'll show up as empty in your SELECT results. Your other datasets might not have this hidden whitespace, which is why the filter works there.

Fix: Use string.IsNullOrWhiteSpace() to catch whitespace, empty strings, and NULLs all at once:

WHERE !string.IsNullOrWhiteSpace(col)

Or trim the value first before checking:

WHERE TRIM(col) != "" AND col IS NOT NULL

3. Data Source Parsing Quirks

If you're reading from a file (like CSV), incorrect parsing settings can turn "empty" values into something unexpected. For example, if your file uses quoted empty values (""), misconfigured quoting settings might read them as a string containing two quotes ("\"\"") instead of a true empty string. This would make col == "" fail, but in SELECT you'd just see an empty-looking value.

Fix: Debug the actual content of your "empty" columns first. Run a quick query to inspect the raw values and their properties:

SELECT 
    col,
    LEN(col) AS column_length,
    col == "" AS is_empty_string,
    col IS NULL AS is_null
FROM your_table
WHERE col IS NULL OR col == "" OR TRIM(col) == ""

This will tell you exactly what you're dealing with—whether it's NULL, a true empty string, or whitespace/quoted values.

4. Query Optimizer Edge Cases

Rarely, U-SQL's query optimizer might apply different logic to WHERE clauses vs. SELECT expressions (especially with partitioned data or large datasets). If your filter works in SELECT but not WHERE, try forcing row-level evaluation by wrapping the condition in a subquery:

SELECT *
FROM (
    SELECT *
    FROM your_table
    WHERE !string.IsNullOrEmpty(col)
) AS filtered_data

Final Notes #

Since this works on other datasets, the issue is almost certainly tied to the specific data in this table—not your syntax. Start with the debug query above to identify exactly what your "empty" values are, then pick the corresponding fix.

内容的提问来源于stack exchange，提问作者user1043144