Include carriage returns when calculating substring
I have some data which is stored in a VARCHAR(MAX) column that contains the control characters CR & LF (CHAR(10) & CHAR(13)).
I have some start and end position numbers that I need to use to extract a substring from the text - but when I use SUBSTRING these control characters are ignored, which results in the substring being extracted incorrectly.
I wrote a query using a CTE that replaced all instances of CRLF with another character (¬¬) and then the substring works correctly - however I need to retain the CRLFs in the text as they are used for display purposes.
Can anyone think of a way I can get the SUBSTRING function to include the control characters when it is calculating which part of the string to extract?
The SQL Server version of substring treats CRLF like other characters. For example:
select substring('123' + char(10) + char(13) + '678',1,3) --> 123 select substring('123' + char(10) + char(13) + '678',4,2) --> \r\n select substring('123' + char(10) + char(13) + '678',6,3) --> 678
Check your code again, or post a more specific example of where substring does not work as expected.