CHARINDEX counting problem

• FreeHansje

  SSChampion

  Points: 11751

  More actions

  May 13, 2009 at 6:44 am

  #130853

  Right, new question. I have a varchar(1000) field with some info I need to extract from. I use CHARINDEX, could have been PATINDEX. I need to find a start and end postion, which I can do. Then a substring(or leftstring) from the startposition towards the end position. I substract End from Start and should get the length, and there it goes wrong.

  Example:

  declare @string varchar(1000)

  SET @string = 'BladieblaDiebladi bladibla blabla, View = AnyViewName_without_a_space Export to ADirectory wher spaces can occur\Filename.csv'

  SELECT charindex('view = ',@string) AS result1,

  charindex('view = ',@string)+7 AS result2,

  charindex(' ',@string,charindex('view = ',@string)+7) As result3

  -- The length, it should be 27 in this example

  SELECT charindex(' ',@string,charindex('view = ',@string)+7) - charindex('view = ',@string)

  Basically what I'm looking for is the pattern View = AnyViewName

  The name AnyViewName ends with the first space to be found after 'View = '. I I run this query the results are correct, these values give the correct positions between which my pattern, the viewname, is to be found. When I substract these positions I get weird results and I cannot grasp what and why this is happening.

  Help any1?

  Greetz,
  Hans Brouwer

• Mark Cowne

  One Orange Chip

  Points: 26983

  More actions

  May 13, 2009 at 6:55 am

  #993650

  Missing a '+7' ??

  i.e.

  SELECT charindex(' ',@string,charindex('view = ',@string)+7)- charindex('view = ',@string)

  should be

  SELECT charindex(' ',@string,charindex('view = ',@string)+7)- (charindex('view = ',@string)+7)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Samuel Vella

  SSCrazy Eights

  Points: 8055

  More actions

  May 13, 2009 at 6:57 am

  #993655

  Your missing another +7

  declare @string varchar(1000)

  SET @string = 'BladieblaDiebladi bladibla blabla, View = AnyViewName_without_a_space Export to ADirectory wher spaces can occur\Filename.csv'

  SELECT charindex('view = ',@string) AS result1,

  charindex('view = ',@string)+7 AS result2,

  charindex(' ',@string,charindex('view = ',@string)+7) As result3

  -- The length, it should be 27 in this example

  SELECT charindex(' ',@string,charindex('view = ',@string)+7) - (charindex('view = ',@string)+7)

• Samuel Vella

  SSCrazy Eights

  Points: 8055

  More actions

  May 13, 2009 at 6:59 am

  #993658

  Beaten to it!

  Thats what I get for testing the code 😛

• FreeHansje

  SSChampion

  Points: 11751

  More actions

  May 13, 2009 at 7:05 am

  #993664

  I could have sworn I used all combinations of +7... I really swear I did...

  I guess I forgot some...

  Tnx all!

  Greetz,
  Hans Brouwer