CHARINDEX(), comma, and stored proc ... puzzle

[warelock]

This oen really has me puzzled. I have this code in a stored proc :

BEGIN

DECLARE @BadPattern2 CHAR(10)
DECLARE @BadPattern3 VARCHAR(10)

SELECT @BadPattern2 = '713,'
SET @BadPattern3 = '713'

SELECT ItemExplanationCodes, @BadPattern2 AS BadPattern
FROM BillItemXML
WHERE CHARINDEX( @BadPattern2, ItemExplanationCodes ) != 0

END

This returns no rows.

The funny thing is that is I use @BadPattern3, I find the rows I want. It's as though including the comma prevents CHARINDEX from working ...

Does anyone know hwy this is happening ? I'd appreciate help !

Thanks.

 

[PHV]

CHARINDEX, as far as I know, is not an ANSI SQL function.
I guess you may repost in a more appropriate forum.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

maybe ItemExplanationCodes doesn't actually contain a comma

r937.com | rudy.ca

 

[gmmastros]

ONE problem with double-posting is that you can leave others hanging...

thread183-1284148

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ManivannanD]

Stop Guys....
@BadPattern2 is declared as Char(10)
so @BadPattern2 = '713,' will be stroed as '713, '
"713," with 6 Spaces...

Hey warelock,
use varchar instead of char for BadPattern2