Strange result with T-SQL CHARINDEX

Question

Strange result with T-SQL CHARINDEX

degrem_m

SSCommitted

Points: 1519
More actions
August 19, 2006 at 10:10 am

#172199

Hi,
I have a crazy experience with CHARINDEX.
DECLARE
@area float
SET
@area = 45.87;
SELECT CHARINDEX('.',@area);
-- result 3 : it's all right
SET
@area = 54545.87;
SELECT CHARINDEX('.',@area);
-- result 6 : it's all right
SET
@area = 9854545.87;
SELECT CHARINDEX('.',@area);
-- result 2 : it's very bad result
Can you help me, please.
Kindest Regards,
degrem_m
Degremont

Viewing 4 posts - 1 through 3 (of 3 total)

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

degrem_m SSCommitted Points: 1519 More actions · Answer 1

Ok I found it,

charindex can be used only on char, nchar, unichar, univarchar, varchar, nvarchar, binary, and varbinary columns; patindex works on char, nchar, unichar, univarchar, varchar, nvarchar, and text columns.

http://msdn2.microsoft.com/fr-fr/library/ms186323.aspx

Kindest Regards,

degrem_m
Degremont

Greg Baethke Default port Points: 1442 More actions · Answer 2

Actually what SQL is doing is casting the float variable into scientific notation for the last value. You can cast the value as varchar and verify this for yourself. So if you actually do CHARINDEX on 'e' you will get a value returned for the last statement. Run this:

DECLARE

@area float

SET

@area = 45.87;

SELECT

CHARINDEX('.',@area);

SELECT

CAST(@area AS varchar(32))

-- result 3 : it's all right

SET

@area = 54545.87;

SELECT

CHARINDEX('7',@area);

SELECT

CAST(@area AS varchar(32))

-- expected result 8 : actual result 0

SET

@area = 9854545.87;

SELECT

CHARINDEX('e',@area);

SELECT

CAST(@area AS varchar(32))

--it is now an explained result.

You'll notice that even the second value is rounded. So if you do a CHARINDEX on '7', the last 'character' for the second value will return a 0 value (character not found).

Hope this too helps,

Greg

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

This is a case of 'implicit' conversions ... it's just like taking 'defaults' ... they work most of the time but cause you issues ocassionally ... it is a programming best practice to use 'explicit' conversions - always. One never knows when such a result will cause an issue.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."