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

[jmv]

Hello Doug,


Thank you very much.

the "kSp" = Space character (with kSq et kDq being ' et ")

> try the omnis SL browser to try your code

That is an excellent suggestion and I will follow it

It is now very much clear.

Best regards,

Jean-Marc

> Le 18 mai 2021 à 17:36, Doug Easterbrook <doug at artsman.com> a écrit :
> 
> 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 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com