Анализ комментариев #Hashtag из записи SQL Server

Я пытаюсь извлечь все комментарии «#» из текстового поля в базе данных SQL Server 2014. Я использую код, который нашел на этом MSDN thread, чтобы найти несколько хэштегов в одной записи и немного изменить ее, чтобы она соответствовала моим потребностям, но я вижу некоторые неожиданные результаты в моем наборе результатов .

Мой T-SQL до сих пор:

IF OBJECT_ID('tempdb..#hashtag') IS NOT NULL DROP TABLE #hashtag;

IF OBJECT_ID('tempdb..#numbers') IS NOT NULL DROP TABLE #numbers;

IF OBJECT_ID('tempdb..#hashtagcounts') IS NOT NULL DROP TABLE #hashtagcounts;

CREATE TABLE #numbers ( N INT );
DECLARE @i INT; 
SET @i = 1;
WHILE @i < 500
BEGIN
    INSERT  #numbers
    VALUES  ( @i );
    SET @i = @i + 1;
END;

CREATE TABLE #hashtag ( tkt_desc VARCHAR(MAX) );

INSERT INTO #hashtag ( tkt_desc )
SELECT  cst.ticketDescription
FROM    dbo.Trending AS [cst]
    WHERE   cst.ticketDescription LIKE '%#%'
            AND LTRIM(RTRIM(cst.ticketDescription)) NOT LIKE '%# %'
            AND cst.ticketDescription NOT LIKE '%BATCH #%'
            AND cst.ticketDescription NOT LIKE '%#[.:''1-9]%';

SELECT  LOWER ('#' + SUBSTRING(tkt_desc, N,
              CASE WHEN CHARINDEX(' ', tkt_desc, N) > 0
                   THEN CHARINDEX(' ', tkt_desc, N) - N
                   ELSE LEN(tkt_desc)
              END)) AS tkt_desc
INTO #hashtagcounts
FROM    #hashtag
    CROSS JOIN #numbers
WHERE   N <= LEN(tkt_desc) AND SUBSTRING(tkt_desc, N - 1, 1) = '#';

SELECT tkt_desc, COUNT(*) AS [Count]
FROM #hashtagcounts
GROUP BY tkt_desc
ORDER BY Count DESC, tkt_desc;

И мой набор данных выглядит так:

tkt_desc              Count
#updateinfo           6
#update               4
#update update        3
#update updated       3
#reprint              2
#callback             1
#nochargereprint      1
#nocostreprint        1
#notes update         1
#paperlicense please  1

У меня возникают проблемы, когда тег #update представлен тремя разными способами. В идеале я бы не хотел включать в хэштег ничего, что следует после пробела ' ', а тег #update должен иметь значение 10.

Моя первоначальная мысль заключалась в том, что, поскольку это текстовое поле, в поле может быть разрыв строки или возврат каретки, поэтому я попытался справиться с этим, заменив SELECT cst.ticketDescription на REPLACE(REPLACE(cst.ticketDescription,CHAR(13),''),CHAR(10),''), но это просто объединяет отдельные слова в один хэштег. См. пример ниже:

#updateinfo    6
#update        4
#updateupdate  3
#updateupdated 3

Любые предложения о том, как я могу достичь желаемого результата? И я включаю некоторые примеры данных ниже на случай, если кто-то захочет поэкспериментировать.

Запрос, который я решил использовать с помощью двух функций/перекрестного применения и очистил операторы LIKE и NOT LIKE:

SELECT  '#' + LOWER(B.RetVal) AS [HashTag] ,
    COUNT(*) AS [Cnt]
FROM    dbo.Common_SupportTickets AS [cst]
    CROSS APPLY [dbo].[udf-Str-Extract]([dbo].[udf-Str-Strip-Control](cst.ticketDescription) + ' ', '#', ' ') AS [B]
WHERE   cst.ticketDescription LIKE '%#%'
    AND LTRIM(RTRIM(REPLACE(cst.ticketDescription,CHAR(13)+CHAR(10),''))) NOT LIKE '%#'
    AND cst.ticketDescription NOT LIKE '%BATCH #%'
    AND cst.ticketDescription NOT LIKE '%#[.:'')1-9]%'
    AND CAST(cst.createDate AS DATE) >= CAST( @paramStartDate AS DATE )
    AND CAST(cst.createDate AS DATE) <= CAST( @paramEndDate AS DATE )
GROUP BY B.RetVal

Примеры данных и записей:

USE [Sandbox]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Trending](
[TicketDescription] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#registrationnotreceived     customer has not received registration for boat...')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#registrationnoreceived      Customer called and still has not received duplicate registration...')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#callback (111) 111-1111

Agent''s POS is briefly turning on before "going to sleep" and entering sleep mode. Agent claims POS will not stay active for any length of time.')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#Update
Corrected last name and driver''s license number.')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#Update
Update customer''s last name.')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#Update
Update last name, address')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#update - Profile updated. Corrected last name.')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#updateinfo')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#updateinfo')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#updateinfo')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#updateinfo')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#question')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#Update
Update residency status')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#update #SSNdiscrepancy 
John Doe called in claiming this was their SSN, please advise. Please contact John Doe at this number (111-111-2222) when the issue is resolved. He wishes to create an account once the issue is resolved.')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#Notes
Update Customer''s Hunter certificate number')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#update
Updated residency status')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#Update
Changed residency from in-state to out-of-state, likely didn''t update.')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#Update
Updated Customer''s last name')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#updateinfo')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#updateinfo')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#void - Agent called in asking to void a duplicate license sale.')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#Update - updated customer''s last name')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#permissions
Changed agent role from AGENT CLERK to AGENT MANAGER in order to order supplies.')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#registrationreprint customer didn''t receive registration I sent to Twra It for reprint. Told to call if he has not received in 10days ')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#printerissue')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#update - Profile updated. Religious Exempt.')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#reprint   this is 2nd call from customer that they have not received there boat registration...')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#registratedincorrectly  He send in check and info from Clerks office beginning of Dec, ')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#reprint #paperlicense Please reprint this license for the customer, he claims he has not yet received it. ')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#nocostreprint customer did not receive boat regst')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#Update
Updated customer''s address over the phone')
GO
INSERT [dbo].[Trending] ([TicketDescription]) VALUES (N'#nochargereprint  ')
GO

Только что заметил странное поведение, когда запускал тот же скрипт для другого диапазона дат — он создавал несуществующие #хэштеги. Например, он возвращает Order: 10 раз, но в поле для #order нет текста:

Образец данных:

CREATE TABLE #temptable ( [ticketDescription] varchar(max), [RetVal] varchar(max) )
INSERT INTO #temptable
VALUES
( 'DURABLE HARD CARD RETURN-WAS GOING TO CALL TO PICK UP  NO# NUMBER ', 'DURABLE' ), 
( 'Order: 30341143OrderItemId: 30517890License for: NATHAN TIMOTHY SUMNER  Printed on: 10 Apr 2017 06:43:57:857 Shipped to: 7650 KIOWA ST, APT #01 MILLINGTON TN 38053 - 3219', 'Order:' ), 
( 'Order: 30341143OrderItemId: 30517890License for: NATHAN TIMOTHY SUMNER  Printed on: 10 Apr 2017 06:43:57:857 Shipped to: 7650 KIOWA ST, APT #01 MILLINGTON TN 38053 - 3219', '01' ), 
( 'Order: 30346281OrderItemId: 30526511License for: STANLEY R ROWLAND  Printed on: 07 Apr 2017 06:22:23:417 Shipped to: 25 COUNTRY WOOD LN # 601 WALNUT MS 38683 - 5367', 'Order:' ), 
( 'Order: 30347906OrderItemId: 30529325License for: DOUGLAS R EASTRIDGE  Printed on: 07 Apr 2017 09:29:51:643 Shipped to: 7980 HUFFS FERRY RD N # R LOUDON TN 37774 - 5910', 'Order:' ), 
( 'Order: 30361947OrderItemId: 30554547License for: BLAKE R HADDON  Printed on: 07 Apr 2017 14:01:42:637 Shipped to: 146 14TH AVE NW # 146 WINCHESTER TN 37398 - 1079', 'Order:' ), 
( 'Order: 30362075OrderItemId: 30554740License for: KYLE  JACKSON  Printed on: 07 Apr 2017 14:04:23:473 Shipped to: 3765 E ANDREW JOHNSON HWY, APT # A4 MORRISTOWN TN 37814 - 6200', 'Order:' ), 
( 'Order: 30369152OrderItemId: 30565137License for: DANIEL JAMES SOLA  Printed on: 09 Apr 2017 07:08:28:683 Shipped to: 3212 MAYES LOOP RD # 1 PIGEON FORGE TN 37863 - 7721', 'Order:' ), 
( 'Order: 30370097OrderItemId: 30566543License for: JAMES D JOHNSON  Printed on: 09 Apr 2017 11:51:37:170 Shipped to: 230 FRANKLIN RD # 907 FRANKLIN TN 37064 - 2256', 'Order:' ), 
( 'Order: 30372876OrderItemId: 30571083License for: MARCOS  CLAUDIO P POLONIATO  Printed on: 06 Apr 2017 08:15:01:093 Shipped to: 295 WINDING RIVER DR, # J SANDY SPRINGS GA 30350 - 1926', 'Order:' ), 
( 'Order: 30396415OrderItemId: 30604206License for: GARY T GOODMAN  Printed on: 07 Apr 2017 15:11:20:317 Shipped to: 1046 GREENBRIAR RD # RD.423 TALBOTT TN 37877 - 9055', 'Order:' ), 
( 'Order: 30405689OrderItemId: 30617970License for: VANCE K JOHNSON  Printed on: 09 Apr 2017 09:25:48:670 Shipped to: 614 GARRISON HOLLOW RD, LOT # 11 ELIZABETHTON TN 37643 - 4897', 'Order:' )

SELECT * FROM #temptable;

DROP TABLE #temptable

ИЗМЕНИТЬ – исправить

Select HashTag = '#'+B.RetVal
      ,Cnt      = Count(*)
 From  #temptable A
 Cross Apply [dbo].[udf-Str-Extract]([dbo].[udf-Str-Strip-Control]('#>>> '+A.TicketDescription)+' ','#',' ') B
 Where B.RetVal <> '>>>'
   and B.RetVal Not Like '[0-9][0-9]'
 Group By B.RetVal
 Order By 1