DROP FUNCTION dbo.KludgeNormalization
GO
CREATE FUNCTION dbo.KludgeNormalization(
   @garbage      NVARCHAR(50)
) RETURNS INT
AS BEGIN
   DECLARE @i INT, @j INT, @k INT
   SET @i = 7 + PatIndex(N'%Ticket [0-9]%', @garbage)
   SET @j = PatIndex(N'%[^0-9]%', SubString(@garbage, @i, 50))
   IF @i < 8 OR 0 = @j SET @k = 0 ELSE SET @k = Cast(SubString(@garbage, @i, @j) AS INT)
   RETURN Cast(@k AS INT)
END
GO

CREATE TABLE #a (
   ItemId		INT
,  ReturnDesc   NVARCHAR(50)
   )

INSERT INTO #a (
   ItemId, ReturnDesc ) VALUES
   (1244, 'Did not want')
,  (1244, 'Wrong color')
,  (3426, 'Return came with ticket 426 ')
,  (3571, 'Ticket 584 was not included')

SELECT *, dbo.KludgeNormalization(ReturnDesc)
   FROM #a
   WHERE  ReturnDesc LIKE '%Ticket%[0-9]%'

DROP TABLE #a