title: MS SQL Server 2008 charindex returns 0 on char string field
body: <pre><code>Select charindex('%  %',CAST(X.MyCharStringField AS NVARCHAR(max))) from X
  </code></pre>
  
  <p>Im figuring out how to split up a field that has two spaces between string values.</p>
  
  <p>example field content</p>
  
  <pre><code>'AFLOP  46.95  46.95  36.95  0  0  '
  </code></pre>
  
  <p>Both charindex and patindex return 0 when used again table varchar field, but return position if I declair a varchar(max) variable and test with that.</p>
  
  <p>I can't figure out how to get this working with the table varchar(max) field.</p>
  
  <p><strong>This Works</strong></p>
  
  <pre><code>Declare @theBefore varchar(100)
  SET @theBefore = 'AFLOP  46.95  46.95  36.95  0  0  '
  select charindex(' ',@theBefore) as spaceIndex,SUBSTRING(@theBefore,0,6)as ITEM_ID
  ,ltrim(rtrim(SUBSTRING(@theBefore,charindex(' ',@theBefore),len(@theBefore)+2))) as BEFORE
  </code></pre>
  
  <p><strong>This does not work, returns 0's for each records char string value</strong></p>
  
  <pre><code>Select charindex('%  %',CAST(X.MyCharStringField AS NVARCHAR(max))) from X
  </code></pre>
  
  <p>Anyone have a clue why ?</p>