This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a TSQL script to count the number of occurrences of a string in SQL Server.
This is a very basic demonstration, but sometimes it helps a lot to do this kind basic analysis using TSQL.
Sometimes, we find that column like Reasons or Remarks has some duplicate words or strings. As we are using this data for our report’s purpose, so it needs to find the total number of occurrences of the duplicate string.
Further, we can perform operations on this like update, delete of that duplicate words.
Below is a small demonstration of this:
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE tbl_FindStrings ( ID INTEGER ,DataString VARCHAR(255) ) GO INSERT INTO tbl_FindStrings VALUES (1,'This is my first demo demo at dbrnd.com') ,(2,'My first demo demo at dbrnd.com demo page') ,(3,'This is my first demo') ,(4,'First demo at dbrnd.com demo page.') ,(5,'My demo page') GO |
Script to find the occurrence of ‘demo’ word:
|
1 2 3 4 5 6 |
SELECT ID ,DataString ,COUNT = (LEN(DataString) - LEN(REPLACE(DataString, 'demo', '')))/LEN('demo') FROM tbl_FindStrings GO |
The Result:
