Programming with dBASE - Value contained in a field

Author

Value contained in a field

Robbie Nott

2006-02-09, 3:23 am

Hi all
Please help

I'd like to show all records where a text value is contained anywhere within
a specified field.
I have a field called keywords and it contains long strings which I have to
search through.

Seems that I can't use the good old $ operator in SQL and reading about "IN"
and "WHERE" doesn't seem to help.
Also thought about the filter but that also won't allow the $

Thanks in advance

Robbie Nott

Ivar B. Jessen

2006-02-09, 3:23 am

On Wed, 8 Feb 2006 22:37:31 +0200, in dbase.programming,
Subject: Value contained in a field,
Message-ID: <airkACPLGHA.1100@news-server>,
"Robbie Nott" <robnott@nospam.mweb.co.za> wrote:

>I'd like to show all records where a text value is contained anywhere within
>a specified field.
>I have a field called keywords and it contains long strings which I have to
>search through.
>
>Seems that I can't use the good old $ operator in SQL and reading about "IN"
>and "WHERE" doesn't seem to help.
>Also thought about the filter but that also won't allow the $

See OLH on localSQL -> String functions:

You may use the LIKE predicate for pattern matching in the WHERE clause:

WHERE <expC> LIKE <pattern expC> [ESCAPE <char>]

In <pattern expC>, the % (percent) character stands for zero or more wildcard
characters, and the _ (underscore) stands for a single wildcard character. To
include either special character as an actual pattern character, specify an
ESCAPE character and precede the wildcard character with that escape character.

See also String functions -> Example:

The following query returns all rows where the letters "n’t" appear in the Title
(e.g. "Can’t", "Won’t", "Ain’t", "Don’t"):

SELECT * FROM BOOKS WHERE TITLE LIKE '%n''t%'

Ivar B. Jessen

Robbie Nott

2006-02-09, 3:23 am

Thanks Ivar

I have devoured what you sent me and now I'm battling to get the value into
a varianble ..

cCust = Form.EntryField1.Value
*
q = new query()
q.DataBase := Form.DataBase1
cCust := "%" + cCust + "%"
q.Sql := "Select * From Customer Where CuRef Like cCust"

This gives an invalid field name error ....

So I'm still stuck - if you don't mind

Thanks again
Robbie

"Ivar B. Jessen" <bergishagen@it.notthis.dk> wrote in message
news:mjmku1thuquc4jv
colsl2vl3k2cfmick6c@
4ax.com...
> On Wed, 8 Feb 2006 22:37:31 +0200, in dbase.programming,
> Subject: Value contained in a field,
> Message-ID: <airkACPLGHA.1100@news-server>,
> "Robbie Nott" <robnott@nospam.mweb.co.za> wrote:
>
>
>
> See OLH on localSQL -> String functions:
>
> You may use the LIKE predicate for pattern matching in the WHERE clause:
>
> WHERE <expC> LIKE <pattern expC> [ESCAPE <char>]
>
> In <pattern expC>, the % (percent) character stands for zero or more
> wildcard
> characters, and the _ (underscore) stands for a single wildcard character.
> To
> include either special character as an actual pattern character, specify
> an
> ESCAPE character and precede the wildcard character with that escape
> character.
>
> See also String functions -> Example:
>
> The following query returns all rows where the letters "n't" appear in the
> Title
> (e.g. "Can't", "Won't", "Ain't", "Don't"):
>
> SELECT * FROM BOOKS WHERE TITLE LIKE '%n''t%'
>
>
> Ivar B. Jessen

Bowen Moursund [DataTech]

2006-02-09, 3:23 am

> I'd like to show all records where a text value is contained anywhere
> within a specified field.
> I have a field called keywords and it contains long strings which I have
> to search through.
>
> Seems that I can't use the good old $ operator in SQL and reading about
> "IN" and "WHERE" doesn't seem to help.
> Also thought about the filter but that also won't allow the $

You can use $ in a canGetRow event handler, e.g.:

oQuery.rowset.IsContained = "whatever"
oQuery.rowset.canGetRow := {||this.IsContained $
this.fields["KeyWords"].value}
oQuery.rowset.first()

--
Bowen Moursund
DataTech
Consulting & Development
http://www.bmmnet.us

Robbie Nott

2006-02-09, 3:23 am

Thanks Bowen, that works perfectly
I'm in awe of your knowledge and thank you for your time

Still like to know how to do the SQL option with the variable in it ( see my
reply to Ivar )

Regards
Robbie

"Bowen Moursund [DataTech]" <bmoursund@no.spam.bmmnet.us> wrote in message
news:OEroZqPLGHA.1100@news-server...
>
> You can use $ in a canGetRow event handler, e.g.:
>
> oQuery.rowset.IsContained = "whatever"
> oQuery.rowset.canGetRow := {||this.IsContained $
> this.fields["KeyWords"].value}
> oQuery.rowset.first()
>
>
> --
> Bowen Moursund
> DataTech
> Consulting & Development
> http://www.bmmnet.us
>

Todd Kreuter

2006-02-09, 3:23 am

Robbie Nott wrote:
>
> Thanks Ivar
>
> I have devoured what you sent me and now I'm battling to get the value into
> a varianble ..
>
> cCust = Form.EntryField1.Value
> *
> q = new query()
> q.DataBase := Form.DataBase1
> cCust := "%" + cCust + "%"
> q.Sql := "Select * From Customer Where CuRef Like cCust"

Try:

q.sql := [Select * From Customer Where CuRef Like '] + cCust + [']

--
Todd Kreuter [dBVIPS]

Robbie Nott

2006-02-09, 3:23 am

Thanks Todd

That's fancy footwork with the brackets !
It's cool and works well

Many ways to do this...
I really have learned a lot tonight

Regards
Robbie

"Todd Kreuter" <tkreuter@dbvips.usa> wrote in message
news:43EA7259.1CBFFDF1@dbvips.usa...
>
>
> Robbie Nott wrote:
>
> Try:
>
> q.sql := [Select * From Customer Where CuRef Like '] + cCust + [']
>
>
>
> --
> Todd Kreuter [dBVIPS]

Todd Kreuter

2006-02-09, 9:23 am

Robbie Nott wrote:
>
> That's fancy footwork with the brackets !
> It's cool and works well

They are just easier to look at ;-)

--
Todd Kreuter [dBVIPS]

Sponsored Links