Pontus Svedberg Pontus Svedberg - 6 days ago 6
SQL Question

SQL combine LIKE & AND & =

var getData = "SELECT * FROM Test WHERE exercise = " + exercise + " AND exerVariName LIKE '[A-Z]%' AND date >= '" + fromDate + "' AND date <= '" + toDate + "'";


I cant seem to figure out why the LIKE part in this line doesn't work! I found on w3schools that you can do LIKE '[a-z]%' if you want all values in that column. But it doesn't work. I don't get any errors but I don't get any results either!

I have this second line for when I dont want all, but a specific one (exerVariName), this one works. (I choose either a specific one or 'All' via a dropdown menu.)

var getDataS = "SELECT * FROM Test WHERE exercise = " + exercise + " AND exerVariName = '" + exerVariName + "' AND date >= '" + fromDate + "' AND date <= '" + toDate + "'";


UPDATE!

I want to write out some data from the db just for view, so I select 'exercise' with radiobuttons, 'exerVariName' with a dropdown menu and 'fromDate/toDate' with a textbox!

<div>
<select name="exerVariName">
<option value="all">All</option>
@foreach (var get in db.Query(getVariName)) {
<option value="@get.exerVariName">@get.exerVariName</option>
}
</select>
</div>


So I get the select option data from a database except the first one that I put there with "all" as the value.

Then I have this code: (thisData is put in the foreach)

var thisData = "";

if (exerVariName == "all") {
thisData = getData;
} else {
thisData = getDataS;
}


So that if the select-option-dropdown value is the "all" one, thisData = getData which is this one with the LIKE, the point of this line is to choose everything in the column from the database which is why I choose all the letters (a-z).

var getData = "SELECT * FROM Test WHERE exercise = " + exercise + " AND exerVariName LIKE '[A-Z]%' AND date >= '" + fromDate + "' AND date <= '" + toDate + "'";


And if I select something specific in the dropdown it will go to the ELSE part of the if statement and use this line

var getDataS = "SELECT * FROM Test WHERE exercise = " + exercise + " AND exerVariName = '" + exerVariName + "' AND date >= '" + fromDate + "' AND date <= '" + toDate + "'";


If you need more info, please tell me!

Answer

the point of this line is to choose everything in the column from the database which is why I choose all the letters (a-z).

var getData = "SELECT * FROM Test WHERE exercise = " + exercise + " AND exerVariName LIKE '[A-Z]%' AND date >= '" + fromDate + "' AND date <= '" + toDate + "'";

I think you are overthinking this. If you want everything (ie. no filter on the column) then omit that part/filter on the clause completely.

"SELECT * FROM Test WHERE exercise = " + exercise + " AND date >= '" + fromDate + "' AND date <= '" + toDate + "'";

Finally this is vulnerable to sql injection attacks, you should use parameterized statements instead of string concatenation. I do not know what language you are using for this but here are some best practices specific to c#: Best Practices - Executing Sql Statements. If you update your question with the language I might be able to procure an example of a parameterized query in that language.

Comments