How to delimit string in SQL?

Don't store such things in a database to begin with. Storing multiple values like this breaks the most fundamental design rule. Use properly designed tables and different fields for different pieces of information.

Besides, SQL is a query language. It's very weak in string manipulation, especially T-SQL. Something that would be easy to do with a regular expression or a multi-character separator in C# or Java is very difficult to do in SQL.

The text has two parts, one of the surrounded by quotes and the rest separated from the first one with :. It's not enough to split it in two. In C# or Java you could split on ]: and trim the first [ character, although that would be slower than a regular expression that extracted the parts. The regular expression \[(?<person>.*?\]:(?<comment>.*) will parse this string and extract two named parts, person and comment.

T-SQL though has no regular expressions and no multi-character split.

• The best you could do is store the data in two separate fields
• If that's not possible, parse the string on the client.
• As a last resort, split on : and hope there are no colons in either part, then trim the quotes:

declare @text varchar(200)='[Larry C-Loan Officer]:Comment entered in Portal logged in as LO';

select REPLACE(REPLACE(value,'[',''),']','')
from string_split(@text,':');