Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

CASE INSENSITIVE SEARCH

7 posts in Windows NT Last posting was on 1997-09-28 12:21:56.0Z
cb Posted on 1997-09-10 18:26:39.0Z
Message-ID: <3416E65F.2097@erols.com>
Date: Wed, 10 Sep 1997 14:26:39 -0400
From: cb <cbangert@erols.com>
X-Mailer: Mozilla 3.01Gold (WinNT; U)
MIME-Version: 1.0
Subject: CASE INSENSITIVE SEARCH
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 9
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5543
Article PK: 1081619

i have never worked with sybase before only informix.
i need to write a 'select from where' clause that takes into account
case insensitivity and partiality. i can't find this anywhere.

e.g. suPER =Superman

in informix i wrote " where name matches
'%[s][S][u][U][p][P][e][E][R][r]*'


thanks a lot!

-Chuck


Bret Halford Posted on 1997-09-10 23:02:57.0Z
Message-ID: <34172721.2781E494@sybase.com>
Date: Wed, 10 Sep 1997 16:02:57 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 4.1.3_U1 sun4m)
MIME-Version: 1.0
To: cb <cbangert@erols.com>
Subject: Re: CASE INSENSITIVE SEARCH
References: <3416E65F.2097@erols.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 25
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5540
Article PK: 1081617


cb wrote:
>
> i have never worked with sybase before only informix.
> i need to write a 'select from where' clause that takes into account
> case insensitivity and partiality. i can't find this anywhere.
>
> e.g. suPER =Superman
>
> in informix i wrote " where name matches
> '%[s][S][u][U][p][P][e][E][R][r]*'
>
> thanks a lot!
>
> -Chuck

You can do something similar in Sybase SQL Server:

select * from heros
where name like "[sS][uU][pP][eE][rR][mM][aA][nN]"

See SQL Server Reference Manual, vol 1, on "Wildcard Characters"

-bret

---------------------------------------------------------------------
| Bret Halford Imagine my disappointment __|
| Sybase Technical Support in learning the true nature __|
| 6400 S. Fiddlers Green Circle of rec.humor.oracle... __|
| Englewood, CO 80111-4954 USA |
============================================================


Stefan Karlsson Posted on 1997-09-23 17:06:27.0Z
From: "Stefan Karlsson" <stefank@sybase.com>
Subject: Re: CASE INSENSITIVE SEARCH
References: <3416E65F.2097@erols.com> <34172721.2781E494@sybase.com>
Organization: Sybase Sverige AB
Message-ID: <01bcc84b$9ef0e0e0$617c0cc1@ppcstefank>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.nt
Date: Tue, 23 Sep 1997 13:06:27 -0400
Lines: 46
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5512
Article PK: 1081591

To improve search speed you should consider installing a case insensitive
sort order. Neither of suggestions below are prone to use index, while
"WHERE col1 LIKE "super%", with case insensitive sort order would consider.

/Stefan
====================================================================
Stefan Karlsson SYBASE Sweden

SYBASE Sverige AB +46-8-58711000phone
Box 1248 (Postal) +46-8-58711054direct
Borgarfjordsgatan 16 (visits) +46-70-5256281mobile/voicemail
164 28 KISTA +46-8-7505420fax
SWEDEN stefank@sybase.com
"Trust me, I know what I'm doing..."
====================================================================

Bret Halford <bret@sybase.com> wrote in article
<34172721.2781E494@sybase.com>...
> cb wrote:
> >
> > i have never worked with sybase before only informix.
> > i need to write a 'select from where' clause that takes into account
> > case insensitivity and partiality. i can't find this anywhere.
> >
> > e.g. suPER =Superman
> >
> > in informix i wrote " where name matches
> > '%[s][S][u][U][p][P][e][E][R][r]*'
> >
> > thanks a lot!
> >
> > -Chuck
>
> You can do something similar in Sybase SQL Server:
>
> select * from heros
> where name like "[sS][uU][pP][eE][rR][mM][aA][nN]"
>
> See SQL Server Reference Manual, vol 1, on "Wildcard Characters"
>
> -bret
>
> ---------------------------------------------------------------------
> | Bret Halford Imagine my disappointment __|
> | Sybase Technical Support in learning the true nature __|
> | 6400 S. Fiddlers Green Circle of rec.humor.oracle... __|
> | Englewood, CO 80111-4954 USA |
> ============================================================
>


Doug Klear Posted on 1997-09-24 11:19:37.0Z
From: "Doug Klear" <doug.klear@bea.doc.gov>
Subject: Re: CASE INSENSITIVE SEARCH
References: <3416E65F.2097@erols.com> <34172721.2781E494@sybase.com> <01bcc84b$9ef0e0e0$617c0cc1@ppcstefank>
Organization: BEA
Message-ID: <01bcc8e4$ade388c0$e80319ac@w3029-a>
X-Newsreader: Microsoft Internet News 4.70.1155
Newsgroups: sybase.public.sqlserver.nt
Date: Wed, 24 Sep 1997 07:19:37 -0400
Lines: 10
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5509
Article PK: 1081588

How about WHERE upper(col1) LIKE "SUPER%"?

Stefan Karlsson <stefank@sybase.com> wrote in article
<01bcc84b$9ef0e0e0$617c0cc1@ppcstefank>...
> To improve search speed you should consider installing a case insensitive
> sort order. Neither of suggestions below are prone to use index, while
> "WHERE col1 LIKE "super%", with case insensitive sort order would

consider.


David Lance Wolf Posted on 1997-09-24 16:29:29.0Z
Message-ID: <34293FE9.C50FD29B@sybase.com>
Date: Wed, 24 Sep 1997 09:29:29 -0700
From: David Lance Wolf <dwolf@sybase.com>
Organization: Sybase Professional Services
X-Mailer: Mozilla 4.02 [en] (Win95; I)
MIME-Version: 1.0
Subject: Re: CASE INSENSITIVE SEARCH
References: <3416E65F.2097@erols.com> <34172721.2781E494@sybase.com> <01bcc84b$9ef0e0e0$617c0cc1@ppcstefank> <01bcc8e4$ade388c0$e80319ac@w3029-a>
Content-Type: multipart/mixed; boundary="------------BC3BCAFB4F599C1F9B7BF46B"
Newsgroups: sybase.public.sqlserver.nt
Lines: 37
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5508
Article PK: 1081586

The problem with that is that once you place an function on the left
side of a WHERE clause, the optimizer no longer considers the where
clause a search argument (SARG) and will not use an index.

Dave Wolf
Sybase Professional Services

Doug Klear wrote:
>
> How about WHERE upper(col1) LIKE "SUPER%"?
>
> Stefan Karlsson <stefank@sybase.com> wrote in article
> <01bcc84b$9ef0e0e0$617c0cc1@ppcstefank>...
> > To improve search speed you should consider installing a case insensitive
> > sort order. Neither of suggestions below are prone to use index, while
> > "WHERE col1 LIKE "super%", with case insensitive sort order would
> consider.


Download VCard vcard.vcf


David Levine Posted on 1997-09-28 12:21:56.0Z
From: "David Levine" <david_levine@opcenter.net>
Subject: Re: CASE INSENSITIVE SEARCH
References: <3416E65F.2097@erols.com> <34172721.2781E494@sybase.com> <01bcc84b$9ef0e0e0$617c0cc1@ppcstefank> <01bcc8e4$ade388c0$e80319ac@w3029-a> <34293FE9.C50FD29B@sybase.com>
Organization: Sony
Message-ID: <01bccc11$f9b17c00$7948c697@levined.opcenter.net>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.nt
Date: Sun, 28 Sep 1997 08:21:56 -0400
Lines: 34
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5500
Article PK: 1081558

One alternative is to create another column with an index, that contains
the Upper() of the search column, created via a trigger. You can then
search on that column and an index will be used (eg. where Upper_Last_Name
like "SUPER%"). It has overhead (another column and index), but doesn't
require a server change like setting the search order to be case
insensitive. If it isn't a common occurrence, I think it is a viable
alternative.

David Levine
david_levine@opcenter.net
http://www.opcenter.net/~david_levine/ <-- PowerBuilder Links

David Lance Wolf <dwolf@sybase.com> wrote in article
<34293FE9.C50FD29B@sybase.com>...
> The problem with that is that once you place an function on the left
> side of a WHERE clause, the optimizer no longer considers the where
> clause a search argument (SARG) and will not use an index.
>
> Dave Wolf
> Sybase Professional Services
>
> Doug Klear wrote:
> >
> > How about WHERE upper(col1) LIKE "SUPER%"?
> >
> > Stefan Karlsson <stefank@sybase.com> wrote in article
> > <01bcc84b$9ef0e0e0$617c0cc1@ppcstefank>...
> > > To improve search speed you should consider installing a case
insensitive
> > > sort order. Neither of suggestions below are prone to use index,
while
> > > "WHERE col1 LIKE "super%", with case insensitive sort order would
> > consider.


Kenneth F. Heiman Posted on 1997-09-10 19:06:02.0Z
X-Newsreader: Microsoft Outlook Express 4.71.0544.0
From: "Kenneth F. Heiman" <kfheiman@gdseng.com>
References: <3416E65F.2097@erols.com>
Subject: Re: CASE INSENSITIVE SEARCH
Date: Wed, 10 Sep 1997 14:06:02 -0500
Lines: 27
X-MimeOLE: Produced By Microsoft MimeOLE Engine V4.71.0544.0
Message-ID: <gNtCQLhv8GA.174@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5542
Article PK: 1081620


>i need to write a 'select from where' clause that takes into account
>case insensitivity and partiality. i can't find this anywhere.
>
>e.g. suPER =Superman
>
>in informix i wrote " where name matches
>'%[s][S][u][U][p][P][e][E][R][r]*'
>

One simple (maybe not the best) way would be to have a where clause like

where UPPER(name) like '%SUPER%'

This will find any occurence of SUPER in the string 'name'
The UPPER function converts a string to uppercase. If you then give your
match string in all caps ( as in the example ), it will be case-insensitive.
The % signs allow the match string to be a substring, as in
"non-super" or "superman", or even "non-superman"

Look up 'upper' in your documentation if you need more details.

Hope this helps

-Ken