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?

Count the `#` in the string – juergen d Sep 03 '19 at 14:38 — juergen d, Sep 03 '19 at 14:38

juergen d · Answer 1 · 2019-09-03T14:52:08.873

2

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

Demo

edited Sep 03 '19 at 14:52

answered Sep 03 '19 at 14:40

juergen d

201,996
37
293
362

1

There is a syntax error in your statement, may want to edit – Jebathon Sep 03 '19 at 14:51

Sebastian Brosch · Accepted Answer · 2019-09-03T15:01:45.463

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

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

2 Answers2

Demo