Case sensitivity/insensitivity select...like '%...%'

Case sensitivity/insensitivity select...like '%...%'

Post by Meade Robbo » Fri, 23 Apr 1999 04:00:00



Simon,

case sensitivity is determined by the sort order; this can only be changed
by reloading the server.

If you are using a code page with case-insensitivity, you cannot use
case-sensitive searches with the LIKE clause. If you ARE using a
case-sensitive code page, you can fake it:



else

Of course, this is just a sample that uses dynamic SQL for demonstration and
lousy performance. Keep in mind that using functions such as UPPER() is a
statement will also kill performance.

Hope this helps...

-Meade


Quote:> Hi,

> I'm am trying to be able to select records using 'like'
> but would like to be able to control whether I'm looking
> for a case sensitive or insensitive string.

> Does anyone know whether this is possible? I have searched
> MSDN and looking at a couple othe SQL sites. I'm using SQL 6.5
> by the way.

> Thanks,
> Simon

 
 
 

Case sensitivity/insensitivity select...like '%...%'

Post by Simon On » Sat, 24 Apr 1999 04:00:00


Hi,

I'm am trying to be able to select records using 'like'
but would like to be able to control whether I'm looking
for a case sensitive or insensitive string.

Does anyone know whether this is possible? I have searched
MSDN and looking at a couple othe SQL sites. I'm using SQL 6.5
by the way.

Thanks,
Simon

 
 
 

Case sensitivity/insensitivity select...like '%...%'

Post by Neil Pik » Sat, 24 Apr 1999 04:00:00


Simon,

 It's only possible if you install SQL Server with a case insensitive sort
order

Quote:> I'm am trying to be able to select records using 'like'
> but would like to be able to control whether I'm looking
> for a case sensitive or insensitive string.

> Does anyone know whether this is possible? I have searched
> MSDN and looking at a couple othe SQL sites. I'm using SQL 6.5
> by the way.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp
 
 
 

Case sensitivity/insensitivity select...like '%...%'

Post by Roy Harv » Sat, 24 Apr 1999 04:00:00


Simon,

Quote:>I'm am trying to be able to select records using 'like'
>but would like to be able to control whether I'm looking
>for a case sensitive or insensitive string.

If SQL Server was installed as case sensitive you will only have case
sensitive comparisons, including like.  And if it was installed as
case insensitive, you will only be able to perform insensitive
comparisons.  All the rules for whether two characters are =, <, or >
are controlled by this choice at installation time.

Roy

 
 
 

Case sensitivity/insensitivity select...like '%...%'

Post by Isaac Bla » Sat, 24 Apr 1999 04:00:00



>I'm am trying to be able to select records using 'like'
>but would like to be able to control whether I'm looking
>for a case sensitive or insensitive string.

>Does anyone know whether this is possible? I have searched
>MSDN and looking at a couple othe SQL sites. I'm using SQL 6.5
>by the way.

This is probably too high a price to pay, but you can have a duplicate
set of columns for that purpose - one for case sensitive searches,
another for case insensitive.  The second set of columns can be
maintained through triggers.  

Another solution would be to convert everything into lower or upper
case in your quieris, but this will be terribly slow because of table
scans.

In both cases the server, of course, must be installed as case
sensitive.

 
 
 

Case sensitivity/insensitivity select...like '%...%'

Post by Koh » Tue, 27 Apr 1999 04:00:00



>Hi,

>I'm am trying to be able to select records using 'like'
>but would like to be able to control whether I'm looking
>for a case sensitive or insensitive string.

>Does anyone know whether this is possible? I have searched
>MSDN and looking at a couple othe SQL sites. I'm using SQL 6.5
>by the way.

>Thanks,
>Simon

there is another workaround where u can use the convert function, e.g. if u
try to select rocords with
the search phrase "Test" in table containing both 'test' and "Test"

select patindex('%Test%',msgsubject), msgsubject from gnawm_messages
where
convert(binary(4),substring(msgsubject,patindex('%Test%',msgsubject),5))=
convert(binary(4),'Test')
and msgid in (select msgid from gnawm_messages where msgsubject like
'%Test%')