Re: Interesting abilities of substring

Sure, we use a user interface widget called "Ajax Dynamic List" from
www.dhtmlgoodies.com.  This replaces the HTML SELECT element.

When a user is sitting on a foreign-key field, such as a PATIENT or
CUSTOMER field, the user can just start typing letters or numbers.  An
AJAX call is made to the back end which searches all columns in a
pre-defined list.  If the list is first_name,last_name,dob, and they
type "ken" they will get everybody whose first name or last name begins
with 'ken', and the date is ignored.  If they type '1991' they will get
everybody born in 1991, and of course '1991-10' returns everybody born
in October 1991.

So it turns out that both string concatenation and substring do some
nifty implicit typing.  This means making the generalized lookup
requires you only to know the list of columns, without also needing to
know their types.

SELECT patient as _value
      ,patient
        || ' - ' || namlst
        || ' - ' || nam1st
        || ' - ' || phone
        || ' - ' || ssn
        || ' - ' || dob as _display   FROM patients WHERE (
SUBSTRING(LOWER(patient) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(namlst) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(nam1st) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(phone) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(ssn) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(dob) FROM 1 FOR 2)='ke') ORDER BY patient
Limit 20

Eventually I will be driven to optimize that, but on tables < 10,000
rows it is still well within the threshhold of the human attention span
of < .5 second or so.

Naz Gassiep wrote:
> This may be useful to me and others, care to paste an example of what
> you mean?
> Thanks,
> - Naz.
>
> Kenneth Downs wrote:
>> Here is something cool that I did not realize postgres's substring()
>> could do.
>>
>> Basically, it "knows what you mean" when you do substrings on dates
>> and numbers, doing an implicit cast for you. This is really nice if
>> you happen to be writing a generalized search system, as it makes the
>> code significantly simpler...
>>
>> That's all, now back to our regularly scheduled mailing list.
>>


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com    www.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010