Another way would be to explicitly cast the collation to a case-sensitive
one.
DECLARE
@BaseString AS VARCHAR(8000)
,@myLetter AS VARCHAR(8000)
SET @BaseString =
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' +
'abcdefghijklmnopqrstuvwxyz' +
'01234567890+-'
SET @myLetter = 'c'
SELECT
CHARINDEX(@myLetter, @BaseString)
AS DefaultCollation
,CHARINDEX(
@myLetter COLLATE SQL_Latin1_General_CP1_CS_AS
,@BaseString COLLATE SQL_Latin1_General_CP1_CS_AS
)
AS CaseSensitiveCollation
Because of collation precedence, you could get by with only explicitly
casting one of the two operands, but by explicitly casting both, you are
removing a source of ambiguity.
Good luck.
Sincerely,
Anthony Thomas
--
[quoted text, click to view] "AlterEgo" <alterego55@dslextreme.com> wrote in message
news:ewjsAVdWHHA.1212@TK2MSFTNGP03.phx.gbl...
> Nirmal,
>
> Converting the varchar to varbinary might work for you:
>
> declare @String1 varchar(8000)
> declare @String2 as varchar(8000)
> set @String1 = 'c'
> set @String2 =
> 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz01234567890+-'
>
> select charindex(cast(@String1 as varbinary(8000)), cast(@String2 as
> varbinary(8000)))
>
> -- Bill
>
> "Nirmal Singh" <nirmal.singh@notthisbittowerhamlets.gov.uk> wrote in
message
> news:O6Cxt7aWHHA.4632@TK2MSFTNGP04.phx.gbl...
> > Sorry, Line 1 below should be
> >
> > Set
> >
@BaseString='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz01234567890
+-'
[quoted text, click to view] > >
> >
> >
> > "Nirmal Singh" <nirmal.singh@notthisbittowerhamlets.gov.uk> wrote in
> > message news:e%235c24aWHHA.1120@TK2MSFTNGP02.phx.gbl...
> >>I have the following (sample) code
> >>
> >> Set @BaseString='ABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890+-'
> >> Set @myLetter='c'
> >> Set @b1=CharIndex(@myLetter, @BaseString)
> >>
> >> @b1 is returning the value 3 instead of 29 because it is
> >> case-insensitive. Can I change this behaviour?
> >>
> >> Changing collations on the installation is NOT an option.
> >>
> >> Nirmal Singh
> >>
> >
> >
>
>
