But now that I realize all the starting positions after flavor code is screwed up, I am not too sure where the mistake is.Īfter about a week of struggling with this problem, and a week of trying to move my execution idea to the suggestions mentioned in the comments, I ended up finding a solution to this problem. WHAT am I doing wrong!! I've tried multiple different combinations of the substring length. For Additions, I get Brin (Substring of 'Cover Brine'). For FlavorCode, I get ut: (substring of 'Cut:'). But if size is RELISHSTOCK, I get RELISHSTOC. ,LTRIM(SUBSTRING(ItemDescription, + 2, - 1))) as Brineįor some reason, the SUBSTRING isn't getting the right substring! I get the Qty and Weight correctly. ,LTRIM(SUBSTRING(ItemDescription, + 2, - 1))) as Additions ,LTRIM(SUBSTRING(ItemDescription, + 2, - 1))) as Cut ,LTRIM(SUBSTRING(ItemDescription, + 2, - 1))) as Size

,LTRIM(SUBSTRING(ItemDescription, + 2, - 1))) as FlavorCode ,LTRIM(SUBSTRING(ItemDescription, + 2, - 1))) as caseQty -2 ,LTRIM(SUBSTRING(ItemDescription, + 2, - 1))) as caseQty -1 Here is my current SQL Query to do this: DECLARE intĭECLARE = CHARINDEX(CHAR(13), ItemDescription, = CHARINDEX(CHAR(13), ItemDescription, + = CHARINDEX(CHAR(13), ItemDescription, + = CHARINDEX(CHAR(13), ItemDescription, + = CHARINDEX(CHAR(13), ItemDescription, = CHARINDEX(CHAR(13), ItemDescription, + = CHARINDEX(CHAR(13), ItemDescription, + = CHARINDEX(CHAR(13), ItemDescription, + = CHARINDEX(CHAR(58), ItemDescription, = CHARINDEX(CHAR(58), ItemDescription, + = CHARINDEX(CHAR(58), ItemDescription, + = CHARINDEX(CHAR(58), ItemDescription, + = CHARINDEX(CHAR(58), ItemDescription, + = CHARINDEX(CHAR(58), ItemDescription, + = CHARINDEX(CHAR(58), ItemDescription, + = CHARINDEX(CHAR(58), ItemDescription, + = CHARINDEX(CHAR(58), ItemDescription, + 1) So here is what my preferred outcome should look like: 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. Here is a part of ASCII interpretation of a value: 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ĠD0A meaning Carriage return and Line feed. So I converted this field to VARBINARY and saw what ASCII codes are in here. This is EXACTLY what the I get when I copy the cell on my results and paste it. I am trying to find the values before each line break. I have a column on a SQL Server table that has a lengthy value with line breaks.

Sql search for ascii character