WinBatch Technical Support Forum

Archived Boards => COM Automation and dotNet => Topic started by: stanl on October 18, 2013, 11:22:58 am

Title: ADO LIKE CLAUSE substitution
Post by: stanl on October 18, 2013, 11:22:58 am
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?
Title: Re: ADO LIKE CLAUSE substitution
Post by: Deana on October 18, 2013, 11:34:20 am
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
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.
Title: Re: ADO LIKE CLAUSE substitution
Post by: stanl on October 18, 2013, 01:47:29 pm
Didn't realize I brought this issue up in 2005.... needs StrReplace(string,"*","%%%%")
 
as the replaced string is itself passed as a string.