I have a column 'cancellationreason' [Data type : text] and it contains text. For E.g 'Created policy due to Policy amendment (different price) on old Policy with Policy Number: HCC12345678' is in one column.
I want to extract HCC123456789 from that text, tried few queries like below :-
SUBSTRING(cancellationreason, CHARINDEX('policy Number', cancellationreason), LEN(cancellationreason))
I am getting error : Argument data type text is invalid for argument 1 of len function.
How to do this? Any help will be greatly appreciated.