Подсчитать количество подстрок, встречающихся в столбце

#sql #sql-server #sql-server-2008

#sql #sql-сервер #sql-server-2008

Вопрос:

У меня есть таблица с идентификатором и полем 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

Текстовое поле здесь представляет собой столбец NVARCHAR, который содержит всю пользовательскую активность, связанную с клиентом, за определенный день (например: всю пользовательскую активность от имени пользователя в день 27/05/2014)

Проблема в том, что мне нужно выяснить, сколько раз пользователь «UserName» регистрировал активность в каждой строке.

До сих пор я полагал, что с помощью чего-то вроде этого:

 (...) WHERE TEXT LIKE ''/05/2014_____________UserName%'

Я могу проигнорировать час и захватить все столбцы с нужной мне датой. Однако это не решает мою проблему: это просто указывает, был ли пользователь авторизован или нет, но мне все еще нужно знать, сколько раз пользователь входил в систему в тот день.

То, что я хотел от этого, похоже на это:

 SELECT ID, CountSubstrings(TEXT, ''/05/2014_____________UserName%') as Count, TEXT
FROM ClientData WHERE TEXT LIKE ''/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

Есть идеи?

Ответ №1:

Вот один из подходов, предполагающий, что имя пользователя окружено пробелами:

 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;

1. Хм… Нет, это не совсем то, что мне нужно. Мне нужно знать, сколько раз пользователь выполнял действия в определенный день. Если строка содержит 3 действия пользователя, но только два в «день фильтра», то «numberOfTimes» должен возвращать 2, а не 3. Это вернет 3.

Ответ №2:

Демонстрация SQL Fiddle

 DECLARE @SearchFilter nvarchar(max) = ''/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

1. Можете ли вы объяснить, почему это работает? Я не знаком с функциями XML.

2. Посмотрите на результат SELECT <a>' REPLACE([TEXT],';','</a><a>') '</a>' FROM ClientData

3. @Анон Хорошо. Я поместил это на свой сервер и попытался запустить. Он выдает мне сообщение о том, что «log_entry не может быть использован напрямую».

4. @ThalesPereira используйте явные имена столбцов вместо SELECT *

Ответ №3:

Простым способом было бы закодировать пользовательскую функцию со скалярным значением SQL, которая возвращает количество подстрок, которые вы хотите найти в предоставленной строке (функция, которая принимает 2 параметра и выполняет подсчет).