title: SQL concat / + operator behaving strangely?
body: <p>So basically to explain my situation I have a program where a user can select code numbers, that are alpha numeric.  These codes are stored in my SQL database as datatype char.</p>
  
  <p>When they select all the codes they want, the program then sends a few parameters(the codes being one of them).  The codes are strung together and look something like this:</p>
  
  <p>',01,1,A3' etc. etc. with commas separating the codes.  I have the comma in front, but changing the comma to the back does not change anything.</p>
  
  <p>the @reasonCode variable is the reason codes strung together.</p>
  
  <p>In my where clause I have a statement that is this:</p>
  
  <pre><code>(@reasonCode = 'ALL') OR 
  ((@reasonCode &lt;&gt; 'ALL' AND (charindex(',' +     ro_reason_code, @reasonCode) &gt; 0)))
  </code></pre>
  
  <p>Basically I want to restrict my results to just those that have those specific reason codes the user selected(among other parameters).  I am trying to achieve that by stringing together the codes, and then searching through them using charindex, seperated by commas.</p>
  
  <p>However I am running into an issue.  Here are the results using a few different variations of reason codes:</p>
  
  <pre><code>',1' = 625 records (correct number)  
  ',01' = 1015(correct number)  
  ',01,1 = 1640(correct number)  
  ',1,01' = 1015(for whatever reason it isn't picking up the 1 reason codes)  
  </code></pre>
  
  <p>That is my issue right there.</p>
  
  <p>When I put the 1 in front of the 01, it doesn't pick up the 1 reason codes.  But if I do it flip-flopped it works fine...</p>
  
  <p>Any ideas as to why this happens?
  (I have tried also using the concat function and get the same results, and also tried forcing everything to be char datatype.)</p>
  
  <p>In the end I would like the same result, regardless if it is ,01,1 or ,1,01.</p>