Re: Re[2]: [GENERAL] case-insensitive like operator - Mailing list pgsql-general

Best way to handle this IMHO is to write a routine that uses Access
direct ODBC stuff to handle queries that must be case insensitive -
either that or use LCase(fieldname) & LCase(searchvalue) in your
Access queries, but this will be slower.

Here's my routine in VB for the former:

Public Function SQLPass(SQL As String, Optional DontLog As Variant)
    Dim wrkODBC As Workspace
    Dim conSearch As Connection
    Dim qdfTemp As QueryDef
    Dim connectstring As String, success As Byte, errornumber As Long

    If IsMissing(DontLog) Then DontLog = False
    DontLog = True
    connectstring = "PUT YOUR CONNECT STRING HERE"
    Set wrkODBC = CreateWorkspace("", "moray", "", dbUseODBC)
    Set conSearch = wrkODBC.OpenConnection("Search", , ,
connectstring)

    Set qdfTemp = conSearch.CreateQueryDef("")

    With qdfTemp
        .Prepare = dbQUnprepare
        .SQL = SQL
        success = 1
        On Error GoTo SQLerror
        .Execute

    End With
    If Not (DontLog) And (Nz(InStr(SQL, "UPDATE"), 0) > 0 Or
Nz(InStr(SQL, "CREATE"), 0) > 0 Or Nz(InStr(SQL, "INSERT"), 0) > 0 Or
Nz(InStr(SQL, "DELETE"), 0) > 0) Then
        LogSQL SQL, success ' log if it is an update or delete or
create or insert query...
    End If
    conSearch.Close
    wrkODBC.Close
    Exit Function
SQLerror:
    errornumber = Err.number
  ' Debug.Print errornumber
    On Error GoTo 0

    If errornumber = 3146 Then
        success = 0
        MsgBox ("ERROR trying to do " & SQL & " in server database.")
        Resume Next
    Else
        success = 0
        If Not (DontLog) = True Then LogSQL SQL, success
        Err.Clear
        Err.Raise (errornumber)
    End If
End Function
----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
----- Original Message -----
From: Peter Eisentraut <peter_e@gmx.net>
To: yura <yura@vpcit.ru>
Cc: pgsql-general <pgsql-general@postgreSQL.org>
Sent: Sunday, January 16, 2000 5:13 PM
Subject: Re: Re[2]: [GENERAL] case-insensitive like operator


On 2000-01-15, yura mentioned:

> Hello Adriaan,
>
> Wednesday, January 12, 2000, 4:24:53 PM, you wrote:
>
> AJ> Postgres has a load of text search operators. The most powerful
is ~*
> AJ> whcih gives you a case insensitive regular expression search.
>
> AJ> Adriaan
>
> Thank you for reply, but i can't use ~* operator, because i don't
> create queries, MS Access generates queries and it creates queries
> with 'like' operator.

The way I read the SQL standard, LIKE matches are always
case-sensitive,
so Access would be in violation.

--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



************