ADO LIKE CLAUSE substitution

[stanl]

This question is in advance of compiling a script. moving to a remote machine and testing.

Access SQL 'Like clauses' generally use * for substitution example: WHERE name LIKE "*sand*n*"

Which might find sandenson, or sandworthingtonguy... etc.  Unfortunately, calling the SQL via ADO requires % rather than *. And because % is a WB substitution element, you would need something like StrReplace("*sand*n*","*","%%").

The script I will compile will make the replacement by reading exisitng SQL using *.  The question concerns the middle * -  would a resulting string of "%sand%n%" as the revised SQL work, or would WB think either sand or n is a variable?

[Deana]

Depends on how the variable containing those percent signs was used. If you are just passing a variable you should not have any issue. however if you attempt to using variable substitution those percent signs could be interpreted by WinBatch as needing variable substitution. For Example:

Code (winbatch) Select Expand

strSample = '*sand*n*'
strSampleRev = StrReplace(strSample,'*','%%')
Message('Good use', strSampleRev)
Message('Good use', '%strSampleRev%' )
Message('Bad use', %strSampleRev% ) ; Notice the use of variable substitution with no quotes.

[stanl]

Didn't realize I brought this issue up in 2005.... needs StrReplace(string,"*","%%%%")

as the replaced string is itself passed as a string.