Applying a LIKE with ignoring the case
-
Hello all Gurus, I have a quick question, I have an ASP.Net application and doing some database connections and Queries using an OleDbAdapter/Connection, now what I’m having a problem with is: I read in a user input (first few letters of a name) then I try to query the database selecting any FirstName that is LIKE the input but ignoring the case. The problem is I can’t control how many characters the user will enter of the Name, and their case, in the mean while some of the data base entries are in small letters and some are CAPS, basically I’m trying to use the LIKE clause with ignoring the case. So far I have something like that
strQuery = "SELECT * FROM MyTabl WHERE FirstName LIKE 'A%' OR FirstName LIKE 'a%' 'This will alow me to select all FirstNames starting with a CAP 'A' or 'a', but that is a set text (the A) I need to compare againist the input.What I'm hopping to get to is somthing that would give me a result ofstrQuery = "SELECT * FROM MyTabl WHERE (ToUperCase FirstName) LIKE (ToUperCase txtBx.Text) 'if in Reguler VB I would have done somting like that: "SELECT * FROM MyTabl WHERE UPPER(FirstName) LIKE UCase(textLetter.Text)I know that this not a Syntax but that is the Idea or Logic I'm trying to apply, So can I use your help here. Thanks in advance for your help. -
Hello all Gurus, I have a quick question, I have an ASP.Net application and doing some database connections and Queries using an OleDbAdapter/Connection, now what I’m having a problem with is: I read in a user input (first few letters of a name) then I try to query the database selecting any FirstName that is LIKE the input but ignoring the case. The problem is I can’t control how many characters the user will enter of the Name, and their case, in the mean while some of the data base entries are in small letters and some are CAPS, basically I’m trying to use the LIKE clause with ignoring the case. So far I have something like that
strQuery = "SELECT * FROM MyTabl WHERE FirstName LIKE 'A%' OR FirstName LIKE 'a%' 'This will alow me to select all FirstNames starting with a CAP 'A' or 'a', but that is a set text (the A) I need to compare againist the input.What I'm hopping to get to is somthing that would give me a result ofstrQuery = "SELECT * FROM MyTabl WHERE (ToUperCase FirstName) LIKE (ToUperCase txtBx.Text) 'if in Reguler VB I would have done somting like that: "SELECT * FROM MyTabl WHERE UPPER(FirstName) LIKE UCase(textLetter.Text)I know that this not a Syntax but that is the Idea or Logic I'm trying to apply, So can I use your help here. Thanks in advance for your help.All you need to do is run a case insensitive query. You can do this by converting the string you want to search into upper case and the result to upper case also. e.g.
dim searchString as string = UCase(textLetter.text) dim qryString as string = "SELECT * FROM MyTabl WHERE UPPER(FirstName) LIKE '" & searchString & "%'" -
All you need to do is run a case insensitive query. You can do this by converting the string you want to search into upper case and the result to upper case also. e.g.
dim searchString as string = UCase(textLetter.text) dim qryString as string = "SELECT * FROM MyTabl WHERE UPPER(FirstName) LIKE '" & searchString & "%'"Thank you very much, It worked. I tried several things but never this format
'" & searchString & "%'can I ask you for a quick explanation of its format. Thanks A lot -
Thank you very much, It worked. I tried several things but never this format
'" & searchString & "%'can I ask you for a quick explanation of its format. Thanks A lotStrings have to enclosed in quotation marks in an sql query. For example, you cannot do
SELECT * FROM table WHERE text = whateveryou must enclose whatever in single quotesSELECT * FROM table WHERE text = 'whatever'Now if you are building your own string in a variable, you need to explicitly enclose them in single quotes.