SQL Query Adjustment for Partial City Name Match

Hii

I've implemented a method for searching the "City" table by a partial city name match using a wildcard search in the SQL query. The objective is to retrieve the associated "CountryId" based on a partial match of the city name provided.

However, upon deployment, an error occurred, preventing the successful execution of the query. The code snippet is structured to perform a wildcard search using the Like clause, but it seems there might be an issue with its implementation.

Here is the code snippet in question:

public string SelectCountryIdByCityName(string CityName)
{
    var result = "";
    var sel = new Select(UserConnection)
            .Column("CountryId")
        .From("City")
        .Where("Name").Like(Column.Parameter("%" + CityName + "%")) as Select;
    result = sel.ExecuteScalar().ToString();
    return result;
}

I am seeking guidance or any insights you may have regarding the correct implementation of a wildcard search in SQL queries for partial city name matches.

Your assistance or suggestions on rectifying this issue would be immensely appreciated.