$O-10.1-SQL : pos('x', strint) in a Where clause ?

[Doug Easterbrook]

hello.

I had a look for the Omnis SQL syntax reference and found it online
https://www.omnis.net/developers/resources/download/manuals/Omnis_SQL_v2api.pdf <https://www.omnis.net/developers/resources/download/manuals/Omnis_SQL_v2api.pdf>


the syntax of the like looks like the following with the admonishment that it does not implement ‘escape’ characters to actually search for a ‘%’ in the string.    a % is a wildcard that means one or more characters.

like_predicate ::=
   column_reference [ NOT ] LIKE literal
The ANSI standard adds an ESCAPE clause to the like_predicate to let you specify an escape character so you can match a % or _; Omnis does not implement this. 


in your code …  I’m not sure what kSP and kSq are

but if it evaluates to
—    Affi_Cours_Suivis LIKE '%/G%'   -or-    
—    NOT  Affi_Cours_Suivis LIKE '%/G%'     
 which should be equivalent to ….    Affi_Cours_Suivis NOT LIKE '%/G%'
meaning the NOT could be in front of the variable or the LIKE and should mean the same thing


then my reading of the omnis sql syntax is that it could work.


try the omnis SL browser to try your code.       I’f I’m not sure about something, I always use pgadmin for psotred to work out harder SQL syntax before I try to code it.  


Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On May 18, 2021, at 6:23 AM, jmv <jmvoegeli at bluewin.ch> wrote:
> 
> The method contains :
> 
> If (tChoix.cX_Cours_Grottes)
> 	If (tChoix.cX_Addition_Cours_Grottes)
> 		Calculate vText as con("(Affi_Cours_Suivis",kSp,"LIKE",kSp,kSq,"%","/G","%",kSq,")")
> 		Calculate vOp as vOp_Or
> 	Else If (tChoix.cX_Exclusion_Cours_Grottes)
> 		Calculate vText as con("( NOT","(Affi_Cours_Suivis",kSp,"LIKE",kSp,kSq,"%","/G","%",kSq,"))")
> 		Calculate vOp as vOp_And
> 	End If
> End If
> 
> and many more like that.
> 
> (I've isolated the "/G" to simplify the change. It looks more than a little awkward !)
> 
> Is that possible ?
> 
> 
> 
>> Le 18 mai 2021 à 15:02, Doug Easterbrook <doug at artsman.com> a écrit :
>> 
>> if you are using postgres,   I’d use
>> 
>> select * from table where col_name not ilike ‘%@%’
>> 
>> 
>> 
>> 
>> 
>> 
>> Doug Easterbrook
>> Arts Management Systems Ltd.
>> mailto:doug at artsman.com
>> http://www.artsman.com
>> Phone (403) 650-1978
>> 
>>> On May 18, 2021, at 5:45 AM, jmv <jmvoegeli at bluewin.ch> wrote:
>>> 
>>> Hello listers,
>>> 
>>> I'm struggling with SQL statement.
>>> 
>>> I want to find records that do not contain a specific character:
>>> 
>>> something like :
>>> 
>>> SELECT * WHERE pos("@",Col_Name) = 0
>>> 
>>> I do not know how to do this.
>>> 
>>> Could you help me ?
>>> 
>>> best regards,
>>> 
>>> Jean-Marc
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com