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?

Answers


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.


Need Your Help

how to simplify this sql query into one

php mysql sql join

Is it possible to put these 3 queries into one sql query?

Replace text inside of square brackets

javascript regex

var a = "[i] earned [c] coin for [b] bonus";