title: SQL Server - CHARINDEX on ASCII values
body: <p>I have a column on a SQL Server table that has a lengthy value with line breaks. I am trying to find the values before each line break. </p>
  
  <p><strong>EXAMPLE:</strong></p>
  
  <pre><code>--Column name: ItemDescription
  
  Case Qty: 12
  Weight: 8 oz.
  Flavor code: PB
  Size: STOCK
  Cut: 1/8" x 1/8" x 3/16"
  Additions: Bells
  Cover Brine #: P1
  Kosher Cert: OU
  Organic Cert: 
  </code></pre>
  
  <p>This is EXACTLY what the I get when I copy the cell on my results and paste it. So I converted this field to <code>VARBINARY</code> and saw what <code>ASCII</code> codes are in here. Here is a part of ASCII interpretation of a value:</p>
  
  <pre><code>43 61 73 65 20 51 74 79 3A 20 31 32 0D0A 57 65 69 67 68 74 3A 20 38 20 6F 7A 2E 0D0A 46
  </code></pre>
  
  <p><code>0D0A</code> meaning Carriage return and Line feed. </p>
  
  <p><strong>PREFERRED OUTCOME:</strong></p>
  
  <p>Now that the data is clear, I am trying to find the value after the colon and before the line break and put it in a new column. </p>
  
  <p>So here is what my preferred outcome should look like:</p>
  
  <p><a href="https://i.stack.imgur.com/3dZGy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3dZGy.png" alt="enter image description here"></a></p>
  
  <p><strong>WHAT I'VE TRIED:</strong> </p>
  
  <p>Here is my current SQL Query to do this:</p>
  
  <pre><code>DECLARE @firstLine int
  DECLARE @secondLine int
  DECLARE @thirdLine int
  DECLARE @fourthLine int
  DECLARE @fifthLine int
  DECLARE @sixthLine int
  DECLARE @seventhLine int
  DECLARE @eighthLine int
  
  
  DECLARE @firstColon int
  DECLARE @secondColon int
  DECLARE @thirdColon int
  DECLARE @fourthColon int
  DECLARE @fifthColon int
  DECLARE @sixthColon int
  DECLARE @seventhColon int
  DECLARE @eighthColon int
  DECLARE @ninethColon int
  DECLARE @itemDesc varchar(MAX)
  
  SELECT 
      @itemDesc = ItemDescription
      ,@firstLine = CHARINDEX(CHAR(13), ItemDescription, 1)
      ,@secondLine = CHARINDEX(CHAR(13), ItemDescription, @firstLine + 1)
      ,@thirdLine =  CHARINDEX(CHAR(13), ItemDescription, @secondLine + 1)
      ,@fourthLine =  CHARINDEX(CHAR(13), ItemDescription, @thirdLine + 1)
      ,@fifthLine =  CHARINDEX(CHAR(13), ItemDescription, @fourthLine+ 1)
      ,@sixthLine =  CHARINDEX(CHAR(13), ItemDescription, @fifthLine + 1)
      ,@seventhLine =  CHARINDEX(CHAR(13), ItemDescription, @sixthLine + 1)
      ,@eighthLine = CHARINDEX(CHAR(13), ItemDescription, @seventhLine + 1)
  
      ,@firstColon = CHARINDEX(CHAR(58), ItemDescription, 1)--aaa
      ,@secondColon = CHARINDEX(CHAR(58), ItemDescription, @firstLine + 1)
      ,@thirdColon = CHARINDEX(CHAR(58), ItemDescription, @secondLine + 1)
      ,@fourthColon = CHARINDEX(CHAR(58), ItemDescription, @thirdLine + 1)
      ,@fifthColon = CHARINDEX(CHAR(58), ItemDescription, @fourthLine + 1)
      ,@sixthColon = CHARINDEX(CHAR(58), ItemDescription, @fifthLine + 1)
      ,@seventhColon = CHARINDEX(CHAR(58), ItemDescription, @sixthLine + 1)
      ,@eighthColon = CHARINDEX(CHAR(58), ItemDescription, @seventhLine + 1)
      ,@ninethColon = CHARINDEX(CHAR(58), ItemDescription, @eighthLine + 1)
  FROM TableName
  
  SELECT 
      ItemDescription
      ,CONVERT(VarBInary, itemDescription)
      ,LTRIM(SUBSTRING(ItemDescription, @firstColon + 2, @firstLine - (@firstColon - 1))) as caseQty          --1
      ,LTRIM(SUBSTRING(ItemDescription, @secondColon + 2, @secondLine - (@secondColon - 1))) as caseQty           --2
      ,LTRIM(SUBSTRING(ItemDescription, @thirdColon + 2, @thirdLine - (@thirdColon - 1))) as FlavorCode
      ,LTRIM(SUBSTRING(ItemDescription, @fourthColon + 2, @fourthLine - (@fourthColon - 1))) as Size
      ,LTRIM(SUBSTRING(ItemDescription, @fifthColon + 2, @fifthLine - (@fifthColon - 1))) as Cut
      ,LTRIM(SUBSTRING(ItemDescription, @sixthColon + 2, @sixthLine - (@sixthColon - 1))) as Additions
      ,LTRIM(SUBSTRING(ItemDescription, @eighthColon + 2, @eighthLine- (@eighthColon - 1))) as Brine
  FROM
      TableName
  </code></pre>
  
  <p><strong>THE ISSUE:</strong></p>
  
  <p>For some reason, the <code>SUBSTRING</code> isn't getting the right substring! I get the <code>Qty</code> and <code>Weight</code> correctly. But if size is <code>RELISHSTOCK</code>, I get <code>RELISHSTOC</code>. For <code>FlavorCode</code>, I get <code>ut:</code> (substring of "Cut:"). For <code>Cut</code>, I get <code>8" x 3/</code>. For <code>Additions</code>, I get <code>Brin</code> (Substring of "Cover Brine").</p>
  
  <p>WHAT am I doing wrong!! I've tried multiple different combinations of the substring length. I know for a fact it is the length. But now that I realize all the starting positions after flavor code is screwed up, I am not too sure where the mistake is. </p>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p>Thank you.</p>