PowerQuery中SQL多标记大小写敏感动态查询问题

Got it, let's fix this dynamic SQL issue for your marker matching. The core problem with your initial attempt is that CONTAINS isn't the right tool here (it's not universally supported and doesn't handle case-sensitive per-character checks easily), plus you weren't splitting your marker string into individual conditions.

Here's a step-by-step approach that works with PowerQuery and maintains case sensitivity:

1. Break Down the Marker Variable #

First, we'll split your VAR_MARKERS string (like "DK") into a list of individual characters. This lets us generate a separate case-sensitive check for each marker.

2. Generate Dynamic Conditions #

For each marker in the list, we'll create a LIKE clause with the COLLATE keyword (just like your working fixed query) to enforce case sensitivity. Then we'll combine all these clauses with AND to ensure all markers are present.

3. Build the Final SQL Query #

We'll stitch everything together into a valid SQL statement, including a safety check for empty marker values.

Full PowerQuery Code Example

// Your existing marker variable (from user selection)
VAR_MARKERS = "DK"

// Replace with your actual table name
TABLE_NAME = "YourCustomerTable"

// Split markers into individual characters
marker_list = Text.ToList(VAR_MARKERS)

// Generate case-sensitive LIKE conditions for each marker
condition_list = List.Transform(marker_list, 
    each "DR_MARKERS COLLATE Latin1_General_BIN LIKE '%" & _ & "%'"
)

// Combine conditions with AND
combined_conditions = Text.Combine(condition_list, " AND ")

// Build final SQL (handle empty marker case to avoid invalid syntax)
final_sql = if Text.Length(VAR_MARKERS) = 0 then
    "SELECT DR_LASTNAME FROM " & TABLE_NAME
else
    "SELECT DR_LASTNAME FROM " & TABLE_NAME & " WHERE " & combined_conditions

Why This Works #

• Case Sensitivity: We keep using COLLATE Latin1_General_BIN just like your working fixed query, so lowercase d won't match uppercase D and vice versa.
• Dynamic Flexibility: This works for any number of markers—whether it's 1, 5, or 10 characters in VAR_MARKERS.
• Valid SQL Syntax: We're building the exact same structure as your tested fixed query, just dynamically. No unsupported CONTAINS calls here.

Important Notes #

• Don't forget to replace YourCustomerTable with the actual name of your SQL table.
• The safety check for empty VAR_MARKERS prevents generating a broken WHERE clause if the user doesn't select any markers.

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