Excel VBA SQL Statement with Contains-CodePudding

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))

CodePudding user response：

Contains is a non-standard SQL function that is available in SQL Server (See https://docs.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.