String Extract

Transact-SQL
1

Hi,

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))

SUBSTRING(cancellationreason,PATINDEX('%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.

Thanks
SG

2

TEXT datatype has been deprecated since SQL2008 and the alternative VARCHAR(MAX) has been available since SQL2005 - i.e. over a decade ...

VARCHAR(MAX) can be used as a parameter to functions like SUBSTRING() / PATINDEX(), but TEXT cannot (in fact TEXT can't be used in pretty much anything useful ... the way in which TEXT data is stored in the database does not lend itself to being post-processed at all)

I recommend that you change the datatype of ALL columns in your DB that use TEXT / Ntext to VARCHAR(MAX) / Nvarchar(MAX)

You can CAST to VARCHAR(MAX), in order to use these functions, but I would strongly discourage you from doing that because performance will be terrible and, given how long it has been deprecated, you really ought to refactor any code that still uses TEXT datatypes

3

You can CAST/Convert this into VARCHAR(MAX)

4

try this:
declare @cancellationreason varchar(250) = 'Created policy due to Policy amendment (different price) on old Policy with Policy Number: HCC12345678'
select
substring(@cancellationreason,charindex(':', @cancellationreason )+2,
len(@cancellationreason)- charindex(':', @cancellationreason))

for Text datatype, as @Kristen suggested