patindex vs charindex

• Hector Angello Silva Carmona

  SSCrazy

  Points: 2720

  More actions

  January 23, 2007 at 8:35 am

  #175542

  patindex vs charindex

  Hello i couldnt find information which is better in perfomance , only i saw the diference, one permits to use wildcard and the other not, thats all i found but i couldnt find informations about the perfomance, i need to use someone in a trigger, which is recommendably to use??

• Michael Gross

  SSC Veteran

  Points: 233

  More actions

  January 23, 2007 at 3:06 pm

  #685073

  I would say that if the functionality is sufficient, then use the charindex.

  If you do:

  DECLARE @sum FLOAT, @s-2 VARCHAR(255)

  SELECT @s-2 = 'foobar', @sum = 0.0

  WHILE @sum < 1000000 BEGIN

  SELECT @sum = @sum + CHARINDEX('o', @s-2)

  END

  SELECT @sum

  and

  DECLARE @sum FLOAT, @s-2 VARCHAR(255)

  SELECT @s-2 = 'foobar', @sum = 0.0

  WHILE @sum < 1000000 BEGIN

  SELECT @sum = @sum + PATINDEX('%o%', @s-2)

  END

  SELECT @sum

  there is no real performance difference.