Xojo Developer Conference
25/27th April 2018 in Denver.
MBS Xojo Conference
6/7th September 2018 in Munich, Germany.

Simpel REALSQLDatabase question (Real Studio network user group Mailinglist archive)

Back to the thread list
Previous thread: Port changes after SendMail call - normal SMTPSocket happening?
Next thread: Controls auto-grouping


Reading *CSV Files versus *.Txt Files   -   Claude Stone
  Simpel REALSQLDatabase question   -   jis
   Re: Simpel REALSQLDatabase question   -   Joe Strout
   Re: Simpel REALSQLDatabase question   -   Keith DeLong
   Re: Simpel REALSQLDatabase question   -   Charles Yeomans
   Re: Simpel REALSQLDatabase question   -   jis
   Re: Simpel REALSQLDatabase question   -   Charles Yeomans
   Re: Simpel REALSQLDatabase question   -   jis
   Re: Simpel REALSQLDatabase question   -   Charles Yeomans
   Re: Simpel REALSQLDatabase question   -   jis
   Re: Simpel REALSQLDatabase question   -   Charles Yeomans
   Re: Simpel REALSQLDatabase question   -   jis
   Re: Simpel REALSQLDatabase question   -   Ruslan Zasukhin

Simpel REALSQLDatabase question
Date: 01.08.08 16:46 (Fri, 1 Aug 2008 17:46:21 +0200)
From: jis
Struggling with my first REALSQLDatabase project, I have two questions.

Is there a simple statement for case sensitive/insensitive searches
and how I could force searches to whole words only?

The LR gives no clue and the NUG shows little information.

Johan

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 17:49 (Fri, 1 Aug 2008 10:49:40 -0600)
From: Joe Strout
On Aug 1, 2008, at 9:46 AM, jis wrote:

> Struggling with my first REALSQLDatabase project, I have two
> questions.
>
> Is there a simple statement for case sensitive/insensitive searches
> and how I could force searches to whole words only?
>
> The LR gives no clue and the NUG shows little information.

That's because these are SQL questions, not RB questions. They'd be
much better targeted to a SQLite (or general SQL database) list.

But, there are certainly people here who know SQL as well as RB. To
do a case-insensitive search, you would store all your text in a
standard case, and then convert your search strings to that same case
before searching.

Not sure how you'd do a whole-word search in standard SQL. Some
database engines extend SQL with RegEx syntax, but I don't know if
SQLite (i.e. REALSQLDatabase) is one of these. But you could always
start with a plain search and then filter the results in code.

Best,
- Joe

Re: Simpel REALSQLDatabase question
Date: 01.08.08 18:18 (Fri, 01 Aug 2008 10:18:21 -0700)
From: Keith DeLong
Hi Johan,

> Struggling with my first REALSQLDatabase project, I have two questions.
>
> Is there a simple statement for case sensitive/insensitive searches

I think you're looking for COLLATION NOCASE
<http://sqlite.org/datatype3.html#collation>

> and how I could force searches to whole words only?

This seems to offer some clues depending on your needs...
<http://www.sqlite.org/cvstrac/wiki?pðllTextIndex>

> The LR gives no clue and the NUG shows little information.

REALSQLDatabase is a wrapper for SQLite. The SQLite docs are here:
<http://sqlite.org/docs.html>

> Johan

HTH,

Keith DeLong


_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 18:19 (Fri, 1 Aug 2008 13:19:06 -0400)
From: Charles Yeomans

On Aug 1, 2008, at 12:49 PM, Joe Strout wrote:

> On Aug 1, 2008, at 9:46 AM, jis wrote:
>
>> Struggling with my first REALSQLDatabase project, I have two
>> questions.
>>
>> Is there a simple statement for case sensitive/insensitive searches
>> and how I could force searches to whole words only?
>>
>> The LR gives no clue and the NUG shows little information.
>
> That's because these are SQL questions, not RB questions. They'd be
> much better targeted to a SQLite (or general SQL database) list.
>
> But, there are certainly people here who know SQL as well as RB. To
> do a case-insensitive search, you would store all your text in a
> standard case, and then convert your search strings to that same
> case before searching.
>

I think in SQLite, a text search using LIKE is case-insensitive.

Charles Yeomans

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 18:56 (Fri, 1 Aug 2008 19:56:14 +0200)
From: jis
Thanks all. I'm studying your suggestions.

I am confused since the LR example uses
LIKE '%" + uppercase(searchTerm) + "%'" for case insensitive searches;
the result without uppercase is the same, however.

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 19:35 (Fri, 1 Aug 2008 14:35:12 -0400)
From: Charles Yeomans

On Aug 1, 2008, at 1:56 PM, jis wrote:

> Thanks all. I'm studying your suggestions.
>
> I am confused since the LR example uses
> LIKE '%" + uppercase(searchTerm) + "%'" for case insensitive searches;
> the result without uppercase is the same, however.


See <http://support.realsoftware.com/listarchives/realbasic-nug/2008-08/msg00014.html
>.

Charles Yeomans

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 20:00 (Fri, 1 Aug 2008 21:00:12 +0200)
From: jis
Hi Charles,

That link returns your earlier message.
What I wanted to say is that with or without the Uppercase-trick, the
result turns out the same: case insentitive.

I think I need to include COLLATE BINARY somewhere, but my efforts
resulted in errors up to now.

Johan

On 01/08/2008, at 8:35 PM, Charles Yeomans wrote:

>
> On Aug 1, 2008, at 1:56 PM, jis wrote:
>
>> Thanks all. I'm studying your suggestions.
>>
>> I am confused since the LR example uses
>> LIKE '%" + uppercase(searchTerm) + "%'" for case insensitive
>> searches;
>> the result without uppercase is the same, however.
>
> See <http://support.realsoftware.com/listarchives/realbasic-nug/2008-08/msg00014.html
> >.
>
> Charles Yeomans

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 20:20 (Fri, 1 Aug 2008 15:20:40 -0400)
From: Charles Yeomans
So you're asking, then, how to do case-sensitive searches?

Charles Yeomans

On Aug 1, 2008, at 3:00 PM, jis wrote:

> Hi Charles,
>
> That link returns your earlier message.
> What I wanted to say is that with or without the Uppercase-trick,
> the result turns out the same: case insentitive.
>
> I think I need to include COLLATE BINARY somewhere, but my efforts
> resulted in errors up to now.
>
> Johan
>
> On 01/08/2008, at 8:35 PM, Charles Yeomans wrote:
>
>>
>> On Aug 1, 2008, at 1:56 PM, jis wrote:
>>
>>> Thanks all. I'm studying your suggestions.
>>>
>>> I am confused since the LR example uses
>>> LIKE '%" + uppercase(searchTerm) + "%'" for case insensitive
>>> searches;
>>> the result without uppercase is the same, however.
>>
>> See <http://support.realsoftware.com/listarchives/realbasic-nug/2008-08/msg00014.html
>> >.
>>
>> Charles Yeomans
>

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 20:30 (Fri, 1 Aug 2008 21:30:55 +0200)
From: jis
Yes, all I get with REALSQL is case insensitive. I need the other one.
When reading Ruslan's note, I wish I had the money to buy Valentina

Johan

On 01/08/2008, at 9:20 PM, Charles Yeomans wrote:

> So you're asking, then, how to do case-sensitive searches?
>
> Charles Yeomans
>
> On Aug 1, 2008, at 3:00 PM, jis wrote:
>
>> Hi Charles,
>>
>> That link returns your earlier message.
>> What I wanted to say is that with or without the Uppercase-trick,
>> the result turns out the same: case insentitive.
>>
>> I think I need to include COLLATE BINARY somewhere, but my efforts
>> resulted in errors up to now.
>>
>> Johan
>>
>> On 01/08/2008, at 8:35 PM, Charles Yeomans wrote:
>>
>>>
>>> On Aug 1, 2008, at 1:56 PM, jis wrote:
>>>
>>>> Thanks all. I'm studying your suggestions.
>>>>
>>>> I am confused since the LR example uses
>>>> LIKE '%" + uppercase(searchTerm) + "%'" for case insensitive
>>>> searches;
>>>> the result without uppercase is the same, however.
>>>
>>>
>>>
>>> See <http://support.realsoftware.com/listarchives/realbasic-nug/2008-08/msg00014.html
>>> >.
>>>
>>> Charles Yeomans

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 20:55 (Fri, 1 Aug 2008 15:55:28 -0400)
From: Charles Yeomans
For REALSQLDatabase, you can use GLOB instead of LIKE. It's case-
sensitive, but uses Unix glob pattern-matching syntax.

SELECT * FROM SomeTable WHERE someColumn GLOB '*foo*'

should find all records where the value of someColumn contains 'foo'
as a substring.

Charles Yeomans


On Aug 1, 2008, at 3:30 PM, jis wrote:

> Yes, all I get with REALSQL is case insensitive. I need the other one.
> When reading Ruslan's note, I wish I had the money to buy Valentina
>
> Johan
>
> On 01/08/2008, at 9:20 PM, Charles Yeomans wrote:
>
>> So you're asking, then, how to do case-sensitive searches?
>>
>> Charles Yeomans
>>
>> On Aug 1, 2008, at 3:00 PM, jis wrote:
>>
>>> Hi Charles,
>>>
>>> That link returns your earlier message.
>>> What I wanted to say is that with or without the Uppercase-trick,
>>> the result turns out the same: case insentitive.
>>>
>>> I think I need to include COLLATE BINARY somewhere, but my efforts
>>> resulted in errors up to now.
>>>
>>> Johan
>>>
>>> On 01/08/2008, at 8:35 PM, Charles Yeomans wrote:
>>>
>>>>
>>>> On Aug 1, 2008, at 1:56 PM, jis wrote:
>>>>
>>>>> Thanks all. I'm studying your suggestions.
>>>>>
>>>>> I am confused since the LR example uses
>>>>> LIKE '%" + uppercase(searchTerm) + "%'" for case insensitive
>>>>> searches;
>>>>> the result without uppercase is the same, however.
>>>>
>>>>
>>>>
>>>> See <http://support.realsoftware.com/listarchives/realbasic-nug/2008-08/msg00014.html
>>>> >.
>>>>
>>>> Charles Yeomans
>
> _______________________________________________
> Unsubscribe or switch delivery mode:
> <http://www.realsoftware.com/support/listmanager/>
> Search the archives:
> <http://support.realsoftware.com/listarchives/lists.html>

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 21:20 (Fri, 1 Aug 2008 22:20:34 +0200)
From: jis
Hi Genius,

It works. Thank you

On 01/08/2008, at 9:55 PM, Charles Yeomans wrote:

> For REALSQLDatabase, you can use GLOB instead of LIKE. It's case-
> sensitive, but uses Unix glob pattern-matching syntax.
>
> SELECT * FROM SomeTable WHERE someColumn GLOB '*foo*'
>
> should find all records where the value of someColumn contains 'foo'
> as a substring.
>
> Charles Yeomans
>

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Simpel REALSQLDatabase question
Date: 01.08.08 20:03 (Fri, 01 Aug 2008 22:03:30 +0300)
From: Ruslan Zasukhin
> From: Joe Strout <<email address removed>>
> Subject: Re: Simpel REALSQLDatabase question
> To: REALbasic NUG <<email address removed>>
> Message-ID: <<email address removed>>
> Content-Type: text/plain; charset=US-ASCII; formatðowed; delsp=yes
>
> On Aug 1, 2008, at 9:46 AM, jis wrote:
>
>> Struggling with my first REALSQLDatabase project, I have two
>> questions.
>>
>> Is there a simple statement for case sensitive/insensitive searches
>> and how I could force searches to whole words only?
>>
>> The LR gives no clue and the NUG shows little information.
>
> That's because these are SQL questions, not RB questions. They'd be
> much better targeted to a SQLite (or general SQL database) list.
>
> But, there are certainly people here who know SQL as well as RB. To
> do a case-insensitive search, you would store all your text in a
> standard case, and then convert your search strings to that same case
> before searching.

Hi Joe,

Usually this not works, because we need save original text case.

In Valentina case-insensitive is easy.
* you store text as is into String/VarChar/Text field.
* define indexed method =lower(fldOriginal)

So you get index which is built on low case. But when you extract found
records, you get original text.

> Not sure how you'd do a whole-word search in standard SQL. Some
> database engines extend SQL with RegEx syntax, but I don't know if
> SQLite (i.e. REALSQLDatabase) is one of these. But you could always
> start with a plain search and then filter the results in code.

Again, easy task with Valentina. It is enough just mention IndexbyWords flag
for string field and Valentina will split strings to words, then index each
word.

-