$select in table

Mon Mar 20 13:37:01 UTC 2023

hi Daniel:

now that you’ve discovered ‘ilike’.   hoa about introducing another diamond in to your postgres repertoire.

by your email address, I think you are in Sweden and that means you likely have to deal with diacritical characters (accented characters).

there is a postgres function called unaccent().    you have to load it an extension to postgres ONCE, then you can use it.

refer to:  https://www.postgresql.org/docs/current/unaccent.html

to install it..  do this one in pgadmin for any database

create extension unaccent

to use it, add unaccent to your search.  (can be used with ilike or any other SQL search

eg

select * from client name where unaccent(client_name) like  unaccent('%Amaz%’)

it means if your client name  was like Hans Högmans släktforskning <https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwi2wKK7z-r9AhUmGTQIHSjsBLYQFnoECAsQAQ&url=http%3A%2F%2Fwww.hhogman.se%2Fswedish_characters.htm&usg=AOvVaw0krTR_XEsv1cGyFL5WON8o>

you can search for it using

select * from client name where unaccent(client_name) like  unaccent(‘%hogmans%’)

or

select * from client name where unaccent(client_name) like  unaccent(‘% Högmans <https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwi2wKK7z-r9AhUmGTQIHSjsBLYQFnoECAsQAQ&url=http%3A%2F%2Fwww.hhogman.se%2Fswedish_characters.htm&usg=AOvVaw0krTR_XEsv1cGyFL5WON8o>%’)

we have used this in our system because we have to deal with both french and english in canada, and many our the customers for our system are around the world.      This just makes it easy for english people (who don’t use diacritical markers) to find non-english names using best guess at the name.

hope its useful.   and since you are building up your searches now, thought it might help down the road

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Mar 20, 2023, at 4:17 AM, Daniel Sananes <daniel.s at kopparbergs.se> wrote:
> 
> Hi Mike
> 
> I am now using ILIKE.
> Works like a brilliant diamond.
> 
> Daniel
> 
> -----Ursprungligt meddelande-----
> Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För Mike Rowan
> Skickat: den 19 mars 2023 01:57
> Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Ämne: Re: $select in table
> 
> Hi Daniel
> Also it can sometimes be useful to use ILIKE rather than LIKE to make the match ignore the case.
> 
> Mike
> 
> On Sun, Mar 19, 2023 at 10:47 AM Scotte Meredith <spomacguy at gmail.com>
> wrote:
> 
>> Look for “bind variables” in the docs to see how they work. A little 
>> tough to explain simply but very powerful.
>> 
>> Sent from my iPad
>> 
>>> On Mar 18, 2023, at 3:14 PM, Daniel Sananes 
>>> <daniel.s at kopparbergs.se>
>> wrote:
>>> 
>>> Thanks Scotte
>>> 
>>> This works: where client_name like '%Amaz%'.
>>> But the @ is confusing and does not work: where client_name like
>> '@%Amaz%'.
>>> 
>>> Daniel
>>> 
>>> -----Ursprungligt meddelande-----
>>> Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För 
>>> Scotte
>> Meredith
>>> Skickat: den 18 mars 2023 20:18
>>> Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
>>> Ämne: Re: $select in table
>>> 
>>> Always start with how the full SQL would look.
>>> In your case:
>>> SELECT * FROM client WHERE client_name LIKE '%somethinghere%'
>>> The $select is the WHERE clause
>>> So put this first:
>>> Calculate clientname = con('%',clientname,'%')
>>> Then:
>>>  where client_name like @[clientname]
>>> 
>>> Using the @ sign makes it a bind variable so the SQL DAM will put in 
>>> the
>> quotes or whatever is needed for the particular SQL field type. Also 
>> can make it more efficient and other things, but that's another story.
>>> Also remember that the $select just sets the data set to use. Then 
>>> you
>> need to $fetch to get 1 at a time, or all at once or 100 at once or 
>> whatever you decide.
>>> 
>>> Scotte Meredith
>>> spomacguy at gmail.com
>>> 509/998-0991
>>> 
>>>> On Mar 18, 2023, at 11:52 AM, Daniel Sananes 
>>>> <daniel.s at kopparbergs.se>
>> wrote:
>>>> 
>>>> Hi all
>>>> I am having problems understanding the $select.
>>>> As I am using tables now I would like to override the table $select.
>>>> I am constructing an order-window now and want to let user enter a 
>>>> part
>> of a clients name to find the client.
>>>> Begin text block
>>>> Text: where client_name LIKE [clientname] End text block Get text 
>>>> block lQuery Do iclientrow.$select(lquery)
>>>> 
>>>> The only thing that works is if I explicitly send "where client_id=1".
>>>> 
>>>> Are there a kind soul out there who could give me an example how an
>> overridden $select would look?
>>>> As I don't know how the inbuilt $select looks I feel lost somehow.
>>>> 
>>>> I guess there are joins and other useful stuff you could have in a 
>>>> $select-method
>>> _____________________________________________________________
>>> Manage your list subscriptions at https://lists.omnis-dev.com Start 
>>> a
>> new message -> mailto:omnisdev-en at lists.omnis-dev.com
>>> VARNING: Det här e-postmeddelandet kommer från en extern avsändare.
>> KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren 
>> och vet att innehållet är säkert.
>>> _____________________________________________________________
>>> Manage your list subscriptions at https://lists.omnis-dev.com Start 
>>> a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com Start a 
>> new message -> mailto:omnisdev-en at lists.omnis-dev.com
>> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> VARNING: Det här e-postmeddelandet kommer från en extern avsändare. KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren och vet att innehållet är säkert.
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 