Bug in charindex?

Question

Post reply

Bug in charindex?

Gerard Pang

SSC Journeyman

Points: 86
More actions
May 24, 2006 at 11:00 pm

#168369

Hi all,
I have a code sample below which seemingly looks fine:

declare
@delimiter varchar(1)
set
@delimiter = char(254)
--set @delimiter = '!'
declare
@string varchar(200)
set
@string = 'Abc' + @delimiter + 'qwe'
--set @string = 'qwertyuiuoipthe' + @delimiter + 'qwe'
declare
@index int
set @index = charindex( @delimiter, @string, 1)
select
substring( @string, 0, @index)

when run it returns 'Abc' as expected. Now switch over to using the
the other @string (with the qwertyuiuoipthe string) and run it again.
The result is truncated at the 'th' of the string. I've tried this
with multiple test strings and it always truncates at a 'th'...

Now try using a different character as the delimiter and it all works...
Anybody ever encounter this? It occurs on both SQL 2000 and SQL 2005.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

I think you may have the 'column result width' set to 14. Can you do the following select and see what it displays:

select 'qwertyuiuoipthe'

If it returns 'qwertyuiuoipth', then you will need to do the following:

In Query Analyser menu, select Tools->Options and then Results tab, then change the 'maximum characters per column' to an appropriate value.

Gerard Pang SSC Journeyman Points: 86 More actions · Answer 2

Hi Paul,

thanks for the reply. I already have the column width set to 256 by default.

I've tried several variations on the string including putting the 'th' at the front

and it always truncates the result at the 'th'.

Any other possible ideas?

cheers, Gerard

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 3

hi,

are you expecting it to return qwertyuiuoipthe ?

that's the result i get on my SQL Server 2000

Paul

Gerard Pang SSC Journeyman Points: 86 More actions · Answer 4

yes, that is supposed to be the expected result. On both my SQL 2000 and SQL 2005

however, I always get qwertyuiuoip only.

Clutching at straws here but could it be some collation setting or unicode related

thing or what? The strange thing is that it only occurs if I use char(254) as the delimiter.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

This is strange indeed, and unfortunately I can't repro it.

On both 2000 and 2005 the example above returns the expected string. No sign of the strange truncate behaviour.

/Kenneth

John Dempsey Hall of Fame Points: 3225 More actions · Answer 6

I also attempted to reproduce the problem on SQL 2000 but was unable too.

When I run your code it returns "qwertyuiuoipthe" as expected.

Sorry that I couldn't be more helpful.

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 7

It's probably not a bug, rather a collation issue. That's why some people can see it and others can't (I can). Try this (including the people who couldn't repro with the original example) and you're likely to see the difference; I get 3 the first time and 1 the second:

declare @delimiter char(1)

set @delimiter = CAST(char(254) AS VARCHAR(1)) 
   COLLATE SQL_Latin1_General_CP1251_CI_AS

select charindex(@delimiter,'th' + @delimiter)

set @delimiter = CAST(char(254) AS VARCHAR(1)) 
   COLLATE Latin1_General_CS_AI

select charindex(@delimiter,'th' + @delimiter)

>L<

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 8

paul_richardson

SSCarpal Tunnel

Points: 4594

May 26, 2006 at 9:57 am

#640366

hi, i still get 3 in both cases ...

Paul

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 9

This might have something to do with our base collations. Mine is Latin1_General_CS_AI. What's yours?

>L<

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 10

(this post may appear twice)

I did a little experimenting and, if I set up a scratch database with a collation of SQL_Latin1_General_Cp1_CI_AS, I don't see the difference. I'm pretty sure this is it. BOL says:

Note The Setup program does not set the instance default collation to the Windows collation Latin1_General_CI_AS if the computer is using the U.S. English locale. Instead, it sets the instance default collation to the SQL collation SQL_Latin1_General_Cp1_CI_AS. This may change in a future release.

... the gentleman with the question may not be set to a U.S. English locale. I am, but I changed the default explicitly during setup (again, mine is Latin1_General_CS_AI).

The point is that if he uses an extended character, or at least this one, he may have to declare its codepage/collation before it will work as a delimiter. It may or may not be a bug, I'm not sure.

>L<

Gerard Pang SSC Journeyman Points: 86 More actions · Answer 11

It is indeed the collation setting. I had mine set to Latin1_General_CS_AI and the

issue occurs with this setting. Setting the collation to something else

(SQL_Latin1_General_CP1251_CI_AS for example) and the issue does not occur.

Thanks everyone for all those great replies.