April 3, 2006 at 6:27 pm
Hi All,
I was using CharIndex function to remove unwanted spaces in a string. Found this peculiar behaviour which made my program go into infinite loop. Here is simple test in SQL Server 2000 Query Analyser
Declare @myname nvarchar(1000)
Set @myname = 'RIGHT HAND'
Select 'RIGHT HAND with one space' RIGHTHAND, CharIndex(' HAND', @myname) Position_SpaceSpaceHAND, CharIndex(' HAND', @myname) Position_SpaceHAND
Set @myname = 'RIGHT HAND'
Select 'RIGHT HAND with two spaces' RIGHTHAND, CharIndex(' HAND', @myname) Position_SpaceSpaceHAND, CharIndex(' HAND', @myname) Position_SpaceHAND
Set @myname = 'RIGHT HAND'
Select 'RIGHT HAND with three spaces' RIGHTHAND, CharIndex(' HAND', @myname) Position_SpaceSpaceHAND, CharIndex(' HAND', @myname) Position_SpaceHAND
Set @myname = 'RIGHT HAND'
Select 'RIGHT HAND with four spaces' RIGHTHAND, CharIndex(' HAND', @myname) Position_SpaceSpaceHAND, CharIndex(' HAND', @myname) Position_SpaceHAND
GO
--- Result pane ---
RIGHTHAND Position_SpaceSpaceHAND Position_SpaceHAND
------------------------- ----------------------- ------------------
RIGHT HAND with one space 0 6
(1 row(s) affected)
RIGHTHAND Position_SpaceSpaceHAND Position_SpaceHAND
-------------------------- ----------------------- ------------------
RIGHT HAND with two spaces 6 7
(1 row(s) affected)
RIGHTHAND Position_SpaceSpaceHAND Position_SpaceHAND
---------------------------- ----------------------- ------------------
RIGHT HAND with three spaces 8 8
(1 row(s) affected)
RIGHTHAND Position_SpaceSpaceHAND Position_SpaceHAND
--------------------------- ----------------------- ------------------
RIGHT HAND with four spaces 9 9
--- End Result pane ---
Can anyone make sense of it? Is it "Works As Designed". I could find it in list of known bugs.
Thanks.
April 3, 2006 at 7:07 pm
That's peculiar 'cos when I ran your statements in QA, this's what I got...
RIGHTHANDPosition_SpaceSpaceHANDPosition_SpaceHAND RIGHT HAND with one space06 RIGHT HAND with two spaces67 RIGHT HAND with three spaces 78 RIGHT HAND with four spaces89
**ASCII stupid question, get a stupid ANSI !!!**
April 3, 2006 at 7:30 pm
It looks kosher to me! What is so peculiar about it?
April 3, 2006 at 7:31 pm
Me too. (Generally when I have used CHARINDEX, I subtract one for replacement or parsing...)
I wasn't born stupid - I had to study.
April 3, 2006 at 7:35 pm
It IS kosher - what's peculiar is the result that Vishwas got...
**ASCII stupid question, get a stupid ANSI !!!**
April 3, 2006 at 9:17 pm
The results I get in my SQL Server 2005 are as per Sushilas finding. But not in SQL Server 2000 (v8.00.194)
April 3, 2006 at 10:28 pm
Result from my computer:
RIGHTHAND Position_SpaceSpaceHAND Position_SpaceHAND
------------------------- ----------------------- ------------------
RIGHT HAND with one space 0 6
(1 row(s) affected)
RIGHTHAND Position_SpaceSpaceHAND Position_SpaceHAND
-------------------------- ----------------------- ------------------
RIGHT HAND with two spaces 6 7
(1 row(s) affected)
RIGHTHAND Position_SpaceSpaceHAND Position_SpaceHAND
---------------------------- ----------------------- ------------------
RIGHT HAND with three spaces 7 8
(1 row(s) affected)
RIGHTHAND Position_SpaceSpaceHAND Position_SpaceHAND
--------------------------- ----------------------- ------------------
RIGHT HAND with four spaces 8 9
(1 row(s) affected)
SELECT @@Version
-----------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
_____________
Code for TallyGenerator
April 5, 2006 at 12:07 pm
Maybe we have a slight difference in behaviour due to version ? Vishwas version is v8.00.194 which is 2000 RTM/No SP, and Sergiv's is 8.00.760 which is 2000 SP3/SP3a. Unfortunately I cannot speak for SQL 2005 differences or the lack ther of. Here's a handy SQL Server version link for future reference:
http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply