Strip characters from text stored as binary image

Transact-SQL
1

Our ERP database (SQL2016) stores long, random length text fields as IMAGE data type. As an example, the text "LINE 35 WAS DELETED AS PER CUSTOMER." is stored in two columns, BITS (0x0D000A004C0049004E00450020003300350020005700410053002000440045004C0045005400450044002000410053002000500045005200200043005500530054004F004D00450052002E002000) and BITS_LENGTH (80).

Note, in this case, the "0D000A00" immediately following the 0x (each character is stored as four bits). This indicates that the text is preceded by a carriage return/line feed. My data (some 100,000 records) contains text fields that may have one or more leading or trailing carriage return/line feeds. I need a query that can strip out these leading/trailing carriage returns without affecting the remaining text and without affecting any carriage return/line feeds that are embedded within the text and overwrite the existing BITS and BITS_LENGTH columns accordingly.

Any assistance would be appreciated.

2

Consumable test data which you should have provided:

CREATE TABLE #t
(
	Bits varbinary(MAX) NOT NULL
	,Bits_length int NOT NULL
);
INSERT INTO #t
VALUES(0x0D000A004C0049004E00450020003300350020005700410053002000440045004C0045005400450044002000410053002000500045005200200043005500530054004F004D00450052002E002000, 80);

select * from #t;

Try something like:

UPDATE T
SET Bits = CAST(X3.Bits AS varbinary(MAX))
	,Bits_Length = DATALENGTH(X3.Bits) + 2
FROM #t T
	CROSS APPLY
	(
		VALUES
		(
			CAST(Bits AS nvarchar(MAX))
		)
	) X1 (Bits)
	CROSS APPLY
	(
		VALUES
		(
			CASE
				WHEN LEFT(X1.Bits, 2) = NCHAR(13) + NCHAR(10)
				THEN SUBSTRING(X1.Bits, 3, LEN(X1.Bits))
				ELSE LTRIM(X1.Bits)
			END
		)
	) X2 (Bits)
	CROSS APPLY
	(
		VALUES
		(
			CASE
				WHEN RIGHT(X2.Bits, 2) = NCHAR(13) + NCHAR(10)
				THEN LEFT(X2.Bits, LEN(X2.Bits) - 2)
				ELSE X2.Bits
			END
		)
	) X3 (Bits);

select * from #t;