Postgres and Searches

[broche]

Clarion 8
Nettalk 8
Postgres 9....

Simple table for Staff members with default sort column of staffname
Data is mixed case, eg Roche, Brian
Have a locator on the table
Set as Starts with works great, type in 'Roc' and it finds the record.
Set as Contains and it starts OK when I type 'R' then filters all records out so none show.
I have the column set as-is, do not have case sensitive on (Either way does not work)
Same applies to Search
I know this is a case sensitive thing but why works on Starts with and not on the others?

Brian

[Bruce]

Turn on "send filter and order to debugview" and see what it says as you type the locator.

[broche]

OK - Did that, the results below

City - is Caps first letter and lower case the rest 'Brookfield'

Entered 'br'
Contains
Case: As-Is
[4540] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( INSTRING('BR',UPPER(sta:city),1,1) <> 0)
Located fine on the first letter 'B' then cleared table on the 'r'

Turned on Case Sensitive Checkbox
Same Result as above
[5592] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( INSTRING('BR',UPPER(sta:city),1,1) <> 0)

Changed setting to Upper
Same
Turned off case sensitive
same

Turned on Case Sensitive Sorting
Contains
As-Is
Case Sensitive Locating
[4844] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( INSTRING('BR',UPPER(sta:city),1,1) <> 0)

Same reult each time always the UPPER on sta:city even though the data is 'Brookfield'

Hope this helps.

Brian

[broche]

Uisng Starts With works
[2560] [st] [netTalk][thread=3] Browsestaff :: Order: UPPER(sta:city),sta:staffsysid
[2560] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( UPPER(sta:city) >= 'BR' AND  UPPER(sta:city) <= 'BRþ')
[2560] [st] [netTalk][thread=3] Browsestaff :: Order: UPPER(sta:city),sta:staffsysid
[2560] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( UPPER(sta:city) >= 'BR' AND  UPPER(sta:city) <= 'BRþ')

The only difference I see is the odd chr at the end of the SQL statement 'þ'

[Bruce]

the other difference is the use of Instring versus not instring. Maybe Postgress doesn't like the Instring, or upper inside the instring.

[broche]

Is there anything I can do about that?

[Bruce]

I'm not the Postgres expert - maybe someone else can jump in here?

cheers
Bruce