SQL case sensitive??

Thread View: borland.public.intrabuilder.data.sql

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by N. van Dijk Thu, 29 Jan 1998 00:00

#44

Author: N. van Dijk
Date: Thu, 29 Jan 1998 00:00

25 lines
1227 bytes

Hi all,

I have a slight problem getting my query workt out all right:
I need to locate the fields in the database according to a keyword
provided by the user. Seem pretty simple I'd say. I first tried
embedding the keyword into the sql property of the query, something
like:
query.sql = "SELECT * FROM Prijsl.dbf WHERE (OMSCHRIJVI LIKE
'%pulse%')";
although this seems to work fine, all the fields with "Pulse" don't
get selected.

To bad, but nothing lost: Just use applyLocate(). However, applylocate
doesn't seem to work with the LIKE '%pulse%', because IB constantly
get back with a : "Unnallowed fhrase/keyword in statment: LIKE
'%pulse%'

Is there anyone who could help me outon this one? I always thought sql
was case INsensative, and I also thouh=ght applyLocat could use LIKE,
so what going wrong?

Thanks in advance,

Niels

Re: SQL case sensitive??

#45

Author: Steve Koterski
Date: Thu, 29 Jan 1998 00:00

41 lines
2271 bytes

On Thu, 29 Jan 1998 21:27:03 GMT, Niels@lx.student.wau.nl (N. van Dijk)
wrote:

>I have a slight problem getting my query workt out all right:
>I need to locate the fields in the database according to a keyword
>provided by the user. Seem pretty simple I'd say. I first tried
>embedding the keyword into the sql property of the query, something
>like:
>query.sql = "SELECT * FROM Prijsl.dbf WHERE (OMSCHRIJVI LIKE
>'%pulse%')";
>although this seems to work fine, all the fields with "Pulse" don't
>get selected.
>
>To bad, but nothing lost: Just use applyLocate(). However, applylocate
>doesn't seem to work with the LIKE '%pulse%', because IB constantly
>get back with a : "Unnallowed fhrase/keyword in statment: LIKE
>'%pulse%'
>
>Is there anyone who could help me outon this one? I always thought sql
>was case INsensative, and I also thouh=ght applyLocat could use LIKE,
>so what going wrong?

The SQL-92 specification defines the assertion of the LIKE predicate to be
case-sensitive. This is the correct behavior.

To compose a query with a case-insensitive WHERE clause LIKE predicate
comparison, simply apply the SQL function UPPER to both the filter column
and the filtering value (literal, parameter, or subquery result). By
putting both in the same casing, you end up with a case-insensitive LIKE
predicate comparison.

  SELECT *
  FROM "Prijsl.dbf"
  WHERE (UPPER(OMSCHRIJVI) LIKE UPPER("%pulse%"))

**************************************************************************
Steve Koterski
Borland International, Inc.
http://www.borland.com/delphi
(Remove the "SPICEDHAM2" from the address. Death to spam-bots!)

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads