Today I learnt a special techinque from SQL Server magazine to find the total number of substrings within a string. The logic is so simple that I am surprised why I never used it.
E.g. abcdballmnopballqrstball
We need to find the total number of occurences of the substring "ball" in the given string.
Logic
--------------
Find the substring ball and replace it with '' e.g.abcdmnopqrst
Find the length of the new string 12
Subtract the new length from the lenth of the original string 24-12 = 12
Divide the new number with the length of the substring 12/4 = 3
Here is the script - taken form the November issue of SQL server magazine
DECLARE
@str AS VARCHAR(1000) = ' abcdballmnopballqrstball ';
@substr AS VARCHAR(1000) = 'ball';
SELECT LEN(@str) - LEN(REPLACE(@str,(@substr,''>/LEN(@substr)
This is so awesome!
No comments:
Post a Comment