In Excel, the Like operator is used with ? and * to designate one or many missing characters, respectively. Someone recently asked me about using the Like operator in MSQuery. Since I didn’t know off the top of my head, I went to the help file. If there’s anything in the help file about this, I couldn’t find it.
In MSQuery, Like works just how you’d expect it to, that is if you’ve used Like in some other program. The difference is that MSQuery’s Like appears to have only one wildcard character, namely the percent sign (%). It stands in for multiple characters, similar to the asterisks (*) in Excel. The criterion
will return all the records that start with”KU”. Like appears to be case insensitive. That is, the above criterion would also return records that start with “ku”, “Ku”, and “kU”.
To use Like in a parameter, simply concatenate the percent sign with your parameter with the ampersand (&). For example, to find all the customers whose CustomerID started with ‘S’ and whose City contained a certain letter, you might set up your criteria like this:
This uses the Northwind sample database’s Customer table. I entered an ‘a’ when the parameter prompted me and got all the CustomerIDs starting with ‘S’ whose City contained an ‘a’.
The wildcard for a single character match is the underscore.
eg.
select * from customers where customerid like ‘_LFKI’
On another note…
Escaping the wildcards can at times become a problem.
Imagine trying to match a value which is like 100%
(where the percent sign is actually what is sought)
Oracle SQL has a mechanism for specifying the escape character.
eg. select * from my_table where myval like ‘% 100\%’ escape ‘ ‘
I’m not certain if ODBC has this facility.
Of course, Like can be used to perform simple pattern matching e.g.
SELECT CustomerID
FROM Customers
WHERE CustomerID LIKE ‘[A-Z][AEIOU][A-Z][AEIOU][A-Z]’
Jamie.
On a bit of a tangent regardng Excel-MSQuery-SQL – some insight from the experts would be appreciated…
I’ve got a spreadsheet, that I didn’t create, with an embedded MSQuery that accesses an Oracle database.
I can edit and run the Query, but what I really want to know is the name of the DB, user id and password.
Any ideas on how to retrieve this info from the spreadsheet?
Thanks, Gareth
Open the corresponding .dqy file with a text editor.
It may be in a directory like:
C:Documents and Settingsuser.nameApplication DataMicrosoftQueries
I am having the same issue. I have an XL file with an embedded query. There is no *.dqy file as just the XL file was given to me and the query is embedded in a sheet. I can save to create the .dqy file but the worksheet doesn’t reference it. Do I have to deleted and recreate the query?
My understanding of dqy files is that they store connection information, but that Excel only reads that information once – when the query is created. The connection information, from Excel’s standpoint, is stored in the workbook. If you were to subsequently change the dqy file, the query would not change. To get the database name, connection string, sql statement, and all the other query goodness, you have to explore the QueryObject object. As described here http://dicks-clicks.com/excel/ExternalData5.htm.
If you deleted and recreated the query, it would get it’s information from your dqy file, but then subsequent changes to the dqy won’t have any effect, so it’s not a very efficient way to edit an embedded query.
If you want an easier way to edit the query information, download JKP’s QueryManager. http://www.jkp-ads.com/Download.asp
Also, if I’m wrong about how Excel works with the dqy file, someone please correct me.
Hi! Thanks a lot for information in this page, help many people’s in world.
I’m trying query using like but I do not succeed.
I’m using the following value Like ‘%’ & [My Parameter] & ‘%’ is correct?
When I run query is shown the fallowing alert message: “The data types varchar and varchar are incopatible in the boolean AND Operator. Statement(s) could be not prepared.
My SQL code is: WHERE (MyTable.MyColumn Like ‘%’ & ? & ‘%’)
What am I doing wrong?
Douglas: If you’re using SQLServer, I think ‘+’ concatenates. If you’re using Access, ‘&’ concatenates. Try
The criteria for SQL and MS Query to find a like for data that ends with 4 digits like a phone number.
Like ‘%’+[input last 4 digits]
What happens if they enter no value in the parameter.
So, we have ‘%’ & [My Parameter] & ‘%’ and the user just hits enter without typing a value. To my mind, this should mean it searches for all records. My query returns nothing, however.
Any Ideas?
Whiz: maybe the double instance of % is interpreted as searching for records literally like ‘%’ ? Untested. Regards, Pete
“WHERE CustomerID LIKE ‘[A-Z][AEIOU][A-Z][AEIOU][A-Z]” as mentioned above is what I need BUT – –
my database field is varchar(6) and Like ’05[0-1]1[2-4][0-2]’ results in a data truncation error.
The operator works fine in Sql Server but Excel won’t take any compare with more than 6 character.
Anyone have any suggestions??????????????????