SQL Server 2012 - search nvarchar field for > 1 like matches

Question

Suppose I have a column containing strings called "Data" (defined as nvarchar(MAX) )

Data contains the following cells:

-------------------------------------
|                data                |
--------------------------------------
| "test data #item test data #match" |
-------------------------------------
| "test data #test"                  |
-------------------------------------

I want to create a query that searches for occurrences with #{something}. However the query returns rows with occurences of 2 or more. So would return:

-------------------------------------
|                data                |
--------------------------------------
| "test data #item test data #match" |
-------------------------------------

Since there are two hashtag items :

e.g.

SELECT * FROM table WHERE data LIKE '%#% AND COUNT > 2 

How would I go about writing such query?

Answer 1

You can use the following solution using DATALENGTH (difference LEN vs. DATALENGTH):

SELECT * FROM table_name 
WHERE (DATALENGTH(data) - DATALENGTH(REPLACE(data, '#', ''))) / DATALENGTH('#') >= 2

demo on dbfiddle.uk

---

You can also create a function on T-SQL to get a smaller query (and better reuse):

-- create the function GetStringCount
CREATE FUNCTION GetStringCount(@strValue VARCHAR(200), @charValue VARCHAR(200))
RETURNS INT
AS
BEGIN
  RETURN (DATALENGTH(@strValue) - DATALENGTH(REPLACE(@strValue, @charValue, ''))) / DATALENGTH(@charValue)
END

-- use the function GetStringCount
SELECT * FROM table_name WHERE dbo.GetStringCount(data, '#') >= 2

Answer 2

select * from your_table
where len(data) - len(replace(data, '#', '')) >= 2

Demo