SQL Server – grab part of string after a value sequence

I have a table called Note with a column named Notes.

Notes
------
{rtf1ansideff0{fonttbl{f0fnilfcharset0 Arial;}}
viewkind4uc1pardlang1033fs20 called insurance company they are waiting to hear from the claimant's attorney

​x
 
Notes------{rtf1ansideff0{fonttbl{f0fnilfcharset0 Arial;}}viewkind4uc1pardlang1033fs20 called insurance company they are waiting to hear from the claimant's attorney​

It has font info in the beginning which I don’t need. I’ve created a new column name final_notes and would like to grab everything after the “fs” plus two characters. The final result would be

final_notes
-----------
 called insurance company they are waiting to hear from the claimant's attorney

 
final_notes----------- called insurance company they are waiting to hear from the claimant's attorney​

Answer

We use PATINDEX to find the first occurrence of fs followed by two digits.

We null it out if we get a 0 i.e. we cannot find the string.

SUBSTRING(Note, NULLIF(PATINDEX('%fs[0-9][0-9]%', Note), 0) + 4, LEN(Note))

 
SUBSTRING(Note, NULLIF(PATINDEX('%fs[0-9][0-9]%', Note), 0) + 4, LEN(Note))​

Advertisement

Answer