SQL Server空格文本匹配失败及ASP.NET MVC下拉列表JS过滤求助

Hey there, let's tackle your two urgent issues step by step since you're new to ASP.NET MVC and pressed for time!

Since you're using jQuery in your view, here's a straightforward solution to remove dropdown options that contain only whitespace:

First, make sure your dropdown has an id attribute (add one if it doesn't, e.g., id="myDropdown"). Then add this script after your jQuery reference:

$(document).ready(function() {
    // Replace "myDropdown" with your actual dropdown ID
    const $dropdown = $('#myDropdown');
    
    // Loop through each option and remove whitespace-only ones
    $dropdown.find('option').each(function() {
        const optionText = $.trim($(this).text());
        if (optionText === '') {
            $(this).remove();
        }
    });

    // If your dropdown loads options dynamically (e.g., via AJAX), run the above code inside your AJAX success callback instead
    // Example AJAX scenario:
    // $.ajax({
    //     url: '/Controller/GetOptions',
    //     success: function(data) {
    //         $dropdown.html(data);
    //         // Re-run the filtering here after new options are added
    //         $dropdown.find('option').each(function() {
    //             const optionText = $.trim($(this).text());
    //             if (optionText === '') {
    //                 $(this).remove();
    //             }
    //         });
    //     }
    // });
});

This code trims the option text (removes leading/trailing spaces) and removes any option that becomes empty after trimming. If your dropdown loads options asynchronously, remember to run this filtering logic after the new options are added to the DOM.

SQL Server's default string comparison ignores trailing spaces (per ANSI standards), which is why you might be getting unexpected matches or misses. Here are two reliable fixes:

Option 1: Match Using Length + Equality #

Force exact matches by comparing both the value and its length. This ensures that values with different whitespace counts are treated as distinct:

-- Example SQL query
SELECT * FROM YourTableName
WHERE YourColumnName = @InputValue
AND LEN(YourColumnName) = LEN(@InputValue)

If you're using Entity Framework in your MVC controller, translate this to LINQ:

var inputValue = "your text with spaces";
var results = db.YourTableName
                .Where(x => x.YourColumnName == inputValue 
                        && x.YourColumnName.Length == inputValue.Length)
                .ToList();

Option 2: Use a Whitespace-Sensitive Collation #

Change the collation for the comparison to one that distinguishes all whitespace (leading, middle, trailing). Use a collation ending with _WS (Whitespace-sensitive):

SELECT * FROM YourTableName
WHERE YourColumnName COLLATE Latin1_General_CS_AS_WS = @InputValue COLLATE Latin1_General_CS_AS_WS

• CS: Case-sensitive (remove if you don't need case matching)
• AS: Accent-sensitive
• WS: Whitespace-sensitive

This ensures that even minor differences in whitespace are respected during comparison.

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