Excel VBA SQL Statement with Contains

Question

I have a table in Excel which I want to use to get some measures via SQL.

This is the first part of my code, which works fine:

Option Explicit

Sub MySQL()

    Dim cn As Object, rs As Object, output As String, sql As String

    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With

Now I can get the amount of entries with a specific condition like:

sql = "SELECT COUNT(ID) FROM [Data$] WHERE [Type] = ""myType"" and [Status] = ""myStatus"""
Set rs = cn.Execute(sql)
MsgBox (rs(0))

Now I would like to use a CONTAINS condition, but this does not work:

sql = "SELECT COUNT(ID) FROM [Data$] WHERE CONTAINS([Type], ""T"")"
Set rs = cn.Execute(sql)
MsgBox (rs(0))

This returns me a 0 but at least no error: sql = "SELECT COUNT(ID) FROM [Data$] WHERE [Type] LIKE ""T""" Do I still have a Syntax issue? — Julian
– Julian, Commented Nov 9, 2021 at 7:56

FunThomas · Accepted Answer · 2021-11-09 08:12:27Z

3

Contains is a non-standard SQL function that is available in SQL Server (See https://learn.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-ver15). It is used to perform fuzzy searches of all kinds.

Contains is not available when you query other data sources (Excel, Access, Oracle...).

If you just look for a substring, you can use the like-operator and use % as wildcard, eg

sql = "SELECT COUNT(ID) FROM [Data$] WHERE [Type] like '%T%' "

'%T%' will find anything containing the letter T
'T%' will find anything starting with the letter T
'%T' will find anything ending with the letter T
(this is, of course, not limited to a single character, you can use '%overflow%' if you want).

Note that different database systems have different rules for case sensitivity. Querying Excel is case insensitive, so like %t% and like %T% return the same result. This is different on other databases.

You should, btw, make it a habit to use single quotes for constant text within an SQL statement. Double quotes work in Excel but are not SQL standard and will fail in most databases.

answered Nov 9, 2021 at 8:12

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Julian Over a year ago

Thanks for clarifying the question regarding CONTAINS. It works now perfectly with LIKE

Collectives™ on Stack Overflow

Excel VBA SQL Statement with Contains

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related