How to split and get list of specific value from string in sql-CodePudding

I have string like:

[Admin User 01/20/2020] User1 comment [Admin User 01/21/2020] User2 comment [Admin User 01/22/2020] User3 comment

I want to extract comments by splitting above string.

Output:

value
--------------
User1 comment
User2 comment
User3 comment

CodePudding user response：

select substring_index('[Admin User 01/20/2020] User1 comment',']',-1);

More detail is here

CodePudding user response：

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT
DECLARE @name1 NVARCHAR(255)
DECLARE @pos1 INT

WHILE CHARINDEX('[', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX('[', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
  if(LEN(@name)>0) 
  begin
    SELECT @pos1  = CHARINDEX(']', @name) 
    SELECT @name1 = SUBSTRING(@name, @pos1 1, len(@name)-1)
    INSERT INTO @returnList 
    SELECT @name1
  end
  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos 1, LEN(@stringToSplit)-@pos)
 END

 SELECT @pos  = CHARINDEX(']', @stringToSplit) 
 SELECT @name = SUBSTRING(@stringToSplit, @pos 1, len(@stringToSplit)-1)
 INSERT INTO @returnList
 SELECT @name
 RETURN
END