SQL Search Case Sensitive ?

[Oppenhiemer]

Hi -
Firstly - sorry for the repeat post (I tried sending this to the Interbase forum - but thought I would perhaps get more feedback here.)

I was under the impression that SQL SQl searches were not case- sensitive. However, after running a query I find that apparently this is not the case. Here is an example of the SQL statement being executed :

SELECT * From tablename WHERE tablename.fieldname LIKE '%Sunday%';

Its the '%Sunday'% bit that seems to be case sensitive. The "Fieldname" in question is of type VARCHAR (50). I have not made any changes to the default behaviour of Interbase itself (so far as Im aware.)

The above statement will return the expected records. But changing the '%Sunday%' to
'%SUNDAY%' or some other form of case change - causes problems. I will point out that the data contained in the database is in the shape of "Sunday".


Anyway, any help would be much appreciated.

Cheers..

Opp.

 

[Oppenhiemer]

I have just been doing a search on the Internet for matters relating to Interbase and case-sensitivity.

The results are rather depressing. It would appear that basically Interbase IS case sensitive. In as much as it does distinguish between lower and uppcase strings.

Im sorry - but why on earth would anyone in their right mind create a case-sensitive SQL database ???? If programmers want to do a case-sensitive comparison on search results - there are plenty of routines to help do this.

But to make the database itself case-sensitive is (IMO) just dam ridiculous! Its is very much the "old-world" approach. Maybe Borland or Firebird will remove this "Feature" in their commercial developments - but in the meantime we are... whats the phrase Im looking for.. stuck with it (well that will do.)

Here are some options I have come across with regards search clauses ..
==========================================
value CONTAINING value
Case insensitive substring search.

value STARTING WITH value
Case sensitive match at beginning of string.

LIKE '%val%' Will find val but fail to find "Val", "vAl","vaL"

Therefor, if I want to find the word "Sunday" (case-insensitive) I would have to either use the "CONTAINING" clause or 2^6 OR statements using the % operator!

==========================================

If anyone knows of the best way to maybe overcome the case-sensitive issues (or knows of a good Interbase SQL reference - as the one that comes with the program is, shall we say - lacking) I would be very grateful.

Cheers..

Opp.

 

[LucieLastic]

Sybase is also case sensitive, just write your query like this:-

SELECT * From tablename
WHERE Upper(tablename.fieldname) = 'SUNDAY';

Assuming Interbase supports the Upper (or ToUpper) command. Not sure which one works with Interbase.

lou

p.s. Isn't SQL Server also case sensitive too?

 

[Oppenhiemer]

HI Lou

Thats a good Idea (the Interbase equivalent is UPPER .) I just tested this and it works !

SELECT * FROM MEDIANAME WHERE UPPER(MEDIANAME.MEDIANAME) LIKE '%SUN%';

That gets all relevant entries. Many thanks (Im a bit slow this week ;-).)

Cheers..

P.S From what I have heard - SQL Server is not case sensitive (but I may be wrong.) And do you use Sybase with Delphi Lou ?

 

[LucieLastic]

hi

Yep, I'm using Sybase, why do you ask ?

lou

 

[svanels]

Nice tip wheez, the way I worked around was to store the data in uppercase.

Regards S. van Els
SAvanEls@cq-link.sr

 

[Oppenhiemer]

Just curious as to what other ppl are using with Delphi. Some time ago I decided to re-write my current programs in Delphi (From Access) and at the time, there were not that many choices with regards which DB to use with it.

It was either Paradox or Interbase (which just became OpenSource. So I went with the latter. And when u said you used sybase, I was curious as to what db components u used to connect it to Delphi ?

Cheers..
Opp.

 

[LucieLastic]

I use the standard DB components. Once the drivers are installed and the ODBC datasource set up, that's it.

Delphi should connect to virtually any database (I think).

lou

 

[Oppenhiemer]

Ahh.. yes, if sybase provide a level 3 ODBC driver then thats great.

Many ODBC drivers I have come across are only level 1 (which means you can only read - not write data.) Sometimes they do provide full access via the ODBC but then want to charge you for it.

Well, thanks again for the info.

Opp.

 

[LucieLastic]

Oppenheimer, I see your problem. Mind you, if you have a licence to use a database (eg, like Sybase, Sqlserv7 etc), you should get the full blown drivers with it, shouldn't you? To be honest, the DBs I've connected to have always had all the drivers 'handy' - maybe someone else had always done the dirty work first of finding out what we need

Just going back to the initial case-sensitivity thingy, can I mention that sorting on a case-sensitive db can sometimes be a pain, so one can use the Upper wotsit in the ORDER BY clause aswell, which I've found extremely useful. If you don't do this, you end up with lower case strings first.

lou

 

[Oppenhiemer]

Hi Lou -

I did look at an option like SQL server etc when thinking of porting my current App's. It is a fine Db of that Im sure, but it was the money that put me off. Same with Oracle (now thats a beast ;-).)

Thats why I was really please when I saw that Interbase had gone Open Source. It meant that here was a client-server SQl Database Free! No Run-time licenses etc to have to pay for (keep track of) And from what I can tell - its a very capable database.

Thats why I was doubly dissapointed when I found out about the case-sensitivity thng. But that tip you gave basically solved that problem.

Anyway, thanks again for your help.

Opp.

P.S This is getting like a chat room .