Подсчитайте количество входящих подстрок в столбце
У меня есть таблица с идентификатором и поле nvarchar.
В каждой строке у меня есть что-то вроде этого:
ID ClientID TEXT 6 1 'Log Entry A' - 26/05/2014 17:32:30; - UserName 'Log Entry B' - 27/05/2014 18:30:30; - UserName 7 2 'Log Entry 2A' - 27/05/2014 17:32:30; - UserName 'Log Entry 2B' - 27/05/2014 18:32:30; - UserName Поле TEXT здесь представляет собой столбец NVARCHAR, который содержит все действия пользователя, связанные с клиентом, в определенный день (например: все действия пользователя от пользователя UserName в день 27/05/2014)
Проблема заключается в следующем: мне нужно выяснить, сколько раз пользователь «UserName» выполнял активность в каждой строке.
До сих пор я полагал, что используя что-то вроде этого:
(...) WHERE TEXT LIKE '%27/05/2014_____________UserName%'
Я могу игнорировать этот час и захватить все столбцы, в которых есть дата, которую я хочу. Однако это не решает мою проблему: это просто дать, если пользователь был зарегистрирован или нет, но мне все еще нужно знать, сколько раз пользователь регистрировался в этот день.
То, что я хотел от этого, похоже на это:
SELECT ID, CountSubstrings(TEXT, '%27/05/2014_____________UserName%') as Count, TEXT FROM ClientData WHERE TEXT LIKE '%27/05/2014_____________UserName%'
Который дал бы мне это в результате:
ID Count TEXT 6 1 'Log Entry A' - 26/05/2014 17:32:30; - UserName 'Log Entry B' - 27/05/2014 18:30:30; - UserName 7 2 'Log Entry 2A' - 27/05/2014 17:32:30; - UserName 'Log Entry 2B' - 27/05/2014 18:32:30; - UserName
Есть идеи?
Вот один подход, предполагая, что имя пользователя окружено пробелами:
select t.*, ((len(' ' + text + ' ') - len(replace(' ' + text + ' ', ' ' + @UserName + ' ', '')) ) / (2 + len(@UserName)) -- the "2" is for the spaces at the beginning and end ) as NumberOfTimes from table t;
Демо-версия SQL Fiddle
DECLARE @SearchFilter nvarchar(max) = '%27/05/2014%' SELECT [ClientID], COUNT(*) [count] FROM ClientData t1 CROSS APPLY(SELECT CAST('<a>'+REPLACE([TEXT],';','</a><a>')+'</a>' AS xml)) t2(xml) CROSS APPLY xml.nodes('a') t3(log_entry) WHERE log_entry.value('.','nvarchar(max)') LIKE @SearchFilter GROUP BY ClientID
Чистым способом было бы кодировать SQL-функцию , определенную пользователем, которая возвращает счетчик подстроки, которую вы хотите найти в предоставленной строке (функция, которая принимает 2 параметра и выполняет подсчет).