I am trying to select some numerals from a long string, which are following some characters that is :RCT. So far I have managed to write this script;

  DECLARE @rct varchar(MAX)
  SET @rct = 'Reallocation of Identified Receiptsv6055161LIVERPOOL SCHOOL OF TROPICAL MEDICINE (LSTM) LONDON(GROUPA8):RCT1122489'

  SELECT SUBSTRING(@rct, CHARINDEX(':RCT', @rct), LEN(@rct)) as RCT

Unfortunately, it returns an empty result. The result I expect is;

  RCT
--------
1122489

1 Answers

1
Pratik Bhavsar On Best Solutions

There might be a more efficient way to return end-index of ':RCT' from the parent string.

But the following does the job for you:

 DECLARE @rct varchar(MAX)
 SET @rct = 'Reallocation of Identified Receiptsv6055161LIVERPOOL SCHOOL OF TROPICAL MEDICINE (LSTM) LONDON(GROUPA8):RCT1122489'

 SELECT SUBSTRING(@rct, CHARINDEX(':RCT', @rct)+LEN(':RCT'), LEN(@rct)) as RCT