#1  
Old 11th July 2018, 06:15 AM
Kyle
 
Posts: n/a
Default SQL statement exclude items that CONTAIN a substring

Back again,

Here is what I usually do in fox pro:

SELECT INVNBR,PAID,COUNT(*) AS CNT FROM ALIAS() WHERE
ATC('TED',INVNBR)=0 AND ATC('*',INVNBR)=0 GROUP BY 1,2

Is there a function in NexusDB SQL that is similar to 'ATC' in fox pro?

Thanks!

---
This email has been checked for viruses by AVG.
https://www.avg.com

  #2  
Old 11th July 2018, 06:32 AM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: SQL statement exclude items that CONTAIN a substring

I've no idea what ATC is supposed to be.

NexusDB implements standard SQL conforming to the SQL:2013 standard.

Based on the subject, you probably want something using the LIKE predicate
(negated with a NOT operator).

Kyle wrote:

> Back again,
>
> Here is what I usually do in fox pro:
>
> SELECT INVNBR,PAID,COUNT(*) AS CNT FROM ALIAS() WHERE
> ATC('TED',INVNBR)=0 AND ATC('*',INVNBR)=0 GROUP BY 1,2
>
> Is there a function in NexusDB SQL that is similar to 'ATC' in fox pro?
>
> Thanks!
>
> ---
> This email has been checked for viruses by AVG.
> https://www.avg.com


  #3  
Old 11th July 2018, 07:20 AM
Kyle
 
Posts: n/a
Default Re: SQL statement exclude items that CONTAIN a substring

Thorsten Engler [NDA] wrote:

> I've no idea what ATC is supposed to be.
>
> NexusDB implements standard SQL conforming to the SQL:2013 standard.
>
> Based on the subject, you probably want something using the LIKE
> predicate (negated with a NOT operator).
>
> Kyle wrote:
>
> > Back again,
> >
> > Here is what I usually do in fox pro:
> >
> > SELECT INVNBR,PAID,COUNT(*) AS CNT FROM ALIAS() WHERE
> > ATC('TED',INVNBR)=0 AND ATC('*',INVNBR)=0 GROUP BY 1,2
> >
> > Is there a function in NexusDB SQL that is similar to 'ATC' in fox
> > pro?
> >
> > Thanks!
> >
> > ---
> > This email has been checked for viruses by AVG.
> > https://www.avg.com


ATC is a function within FoxPro to search for a substring within a
string. Can also be used for blob memos.

For example..

if I had the string 'Hello there, how are you doing?'

and I ran ATC('doing',<string>) it would return 26.

if I ran ATC('xyz',<string>) it would return 0 (not found)


I ended up using the 'POSITION' SQL function (seems to work).

I am leery of using 'LIKE', as I am not really certain what it is
doing, and I am sure it would slow down the query quite a bit.
  #4  
Old 11th July 2018, 07:25 AM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: SQL statement exclude items that CONTAIN a substring

Kyle wrote:

> I ended up using the 'POSITION' SQL function (seems to work).
>
> I am leery of using 'LIKE', as I am not really certain what it is
> doing, and I am sure it would slow down the query quite a bit.


LIKE should't be any slower than POSITION

and generally speaking, all functions that operate on string data are going to
work on both [N][VAR]CHAR and [N]CLOB typed strings.
  #5  
Old 12th July 2018, 01:09 AM
jhoehne jhoehne is offline
Junior Member
 
Join Date: Apr 2018
Posts: 10
Default Re: SQL statement exclude items that CONTAIN a substring

With FoxPro-"SQL" you can do some pretty special stuff, e.g. a query using aggregates, where the "group by" clause doesn't contain all grouping fields:

select count(*), field1, field2 from table
group by field1


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exclude files from nxBackupController backup Andrew Sowerby nexusdb.public.support 9 22nd April 2018 05:42 PM
Exclude blob file from backup Sarotech nexusdb.public.support 6 29th March 2017 10:54 AM
SQL to return items in one table but not another.... Phil Corley nexusdb.public.support.sql 12 9th May 2006 11:46 AM
Cant Exclude Null Ken Randall nexusdb.public.support.sql 7 23rd September 2005 01:18 PM
Wish list items JC Boggio nexusdb.public.support 1 24th September 2003 02:38 AM


All times are GMT +11. The time now is 11:23 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.