Use (DATALENGTH(@columnToSearch) - DATALENGTH(REPLACE(@columnToSearch, @pattern, ''))) / DATALENGTH(@pattern) for count matches
2013-11-29
2,163 reads
cold872wash811,
2015-06-08 (first published: 2015-05-18)
Use (DATALENGTH(@columnToSearch) - DATALENGTH(REPLACE(@columnToSearch, @pattern, ''))) / DATALENGTH(@pattern) for count matches
-- MS SQL Server function LEN() does not count trailing spaces! DECLARE @pattern varchar(20) = 'Ted '; DECLARE @columnToSearch VARCHAR(1000) = 'Ted is Ted because Ted is awesome!'; SELECT (DATALENGTH(@columnToSearch) - DATALENGTH(REPLACE(@columnToSearch, @pattern, ''))) / DATALENGTH(@pattern) DatalengthNumberOfMatches , (LEN(@columnToSearch) - LEN(REPLACE(@columnToSearch, @pattern, ''))) / Len(@pattern) AS LenNumberOfMatches , (LEN(@columnToSearch) - LEN(REPLACE(@columnToSearch, @pattern, ''))) AS LenPattern , LEN(@pattern) AS Len@pattern , DATALENGTH(@pattern) AS Datalength@pattern;
You rated this post out of 5. Change rating
2013-11-29
2,163 reads
2013-11-21
2,142 reads
2013-11-15
1,987 reads
2012-06-07
2,003 reads