Author Topic: ADO LIKE CLAUSE substitution  (Read 2153 times)

stanl

  • Pundit
  • *****
  • Posts: 772
ADO LIKE CLAUSE substitution
« 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?

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: ADO LIKE CLAUSE substitution
« Reply #1 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.
Deana F.
Technical Support
Wilson WindowWare Inc.

stanl

  • Pundit
  • *****
  • Posts: 772
Re: ADO LIKE CLAUSE substitution
« Reply #2 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.