-- PatternSplitLoop will split a string based on a pattern of the form
-- supported by LIKE and PATINDEX
--
-- Created by: Dwain Camps 11-Oct-2012
CREATE FUNCTION [dbo].[PatternSplitLoop]
 (  @String    VARCHAR(400)
   ,@Pattern   VARCHAR(500)
  ) RETURNS
  @Results TABLE ( ItemNumber  INT
                  ,Item        VARCHAR(400)
                  ,[Matched]   INT     )
   WITH SCHEMABINDING
AS
BEGIN;

-- DECLARE a couple of variables we'll need in our loop
DECLARE
  @ItemNumber  INT = 0
, @Remaining   VARCHAR(400) = ISNULL(@String, '')
-- Create the "not pattern"
, @NotPattern  VARCHAR(500) = REPLACE(REPLACE(@Pattern, '[', '[^'), '^^', '')
, @Matched     INT

IF @String IS NULL OR @Pattern IS NULL
  INSERT INTO @Results SELECT NULL, NULL, NULL

WHILE DATALENGTH(@Remaining) > 0
 BEGIN
   SELECT @ItemNumber = @ItemNumber + 1
   -- The item returned from the cascaded CROSS APPLY b below
    ,@String = CASE
            -- When a+b = 1, then either a=1 and b=0 (the pattern was found but not pattern
            -- was not found) or a=0 and b=1 (the not pattern was found but pattern was
            -- not found).
            -- This means that no meaninful patterns are found in what remains so we’re done.
               WHEN a+b = 1 THEN @Remaining
            -- This case returns the chunk up to the start of the next pattern/not pattern
               WHEN (a=1 AND b>0) OR (b=1 AND a>0)                                  THEN SUBSTRING(@Remaining, 1, CASE a
                                  WHEN 1 THEN b
                                  ELSE a
                                 END - 1)
               ELSE @Remaining
                END
           ,@Matched=CASE a WHEN 1 THEN 1 ELSE 0 END                    FROM (
       -- Find the next occurrence of the Pattern and the NotPattern
        SELECT PATINDEX(@Pattern, @Remaining)
             , PATINDEX(@NotPattern, @Remaining)
           ) a(a, b)

       -- Now that we have our ItemNumber and Item (in @String) INSERT them into our results
       INSERT INTO @Results SELECT @ItemNumber, @String, @Matched

        -- Find the remaining characters in the string
        SELECT @Remaining = CASE
                         WHEN DATALENGTH(@Remaining) = DATALENGTH(@String) THEN ''
                         ELSE SUBSTRING(@Remaining, DATALENGTH(@String)+1, DATALENGTH(@Remaining))
         END
 END

RETURN
END

DECLARE @data TABLE
(
 ItemNumber INT,
 Value VARCHAR(500),
 Formatted VARCHAR(100),
 Combined VARCHAR(500)
)

INSERT INTO @data
SELECT ItemNumber, Input, CASE WHEN Matched = 1 AND LTRIM(RTRIM(Item)) != '' THEN '[' + LTRIM(RTRIM(Item)) + ']' ELSE Item END as Arranged, '' FROM TABLE
  CROSS APPLY PatternSplitLoop(Input, '%[a-zA-Z ]%')

DECLARE @tmp TABLE(
    ID INT IDENTITY(1,1),
    DistinctValue VARCHAR(500)
  )

  INSERT INTO @tmp(DistinctValue)
  SELECT Distinct Value FROM @data

DECLARE @minID INT = 1
  DECLARE @maxID INT = 0
  SELECT @maxID = COUNT(ID) FROM @tmp
  DECLARE @currValue VARCHAR(500) = ''

  WHILE (@minID <= @maxID)
  BEGIN

    SELECT @currValue = DistinctValue FROM @tmp WHERE ID = @minID

    Update @data Set Combined = substring(
        (
            Select ' '+Formatted  AS [text()]
            From @data a WHERE Value = @currValue
            ORDER BY a.ItemNumber
            For XML PATH ('')
        ), 2, 1000) FROM @data a INNER JOIN @tmp b ON a.Value = b.DistinctValue AND a.Value = @currValue
        WHERE ItemNumber = 1

   SET @minID = @minID + 1
   SET @currValue = ''
  END

  SELECT Value AS Input, REPLACE(Combined, '&#x20;', ' ') As Result FROM @data WHERE ItemNumber = 1