SQL Query | How many times a substring occurs in a value ? | REPLACE | LEN

In this video, we write a query to count the number of occurrences of a substring.
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.com/company/know...
Email - [e-mail]

[@jameshogge]

Interesting way of doing it. I think I'd do something like this to avoid the division (not for performance, I just think it's more readable)
SELECT (len(replace(@string,@rep,@rep+'*')) - len(@string))

[@user-rv9op2kq4r]

Thanks for your videos.
Note that in MSSQL the Len function excludes trailing spaces at the end of a string. if your input string is like "yes no yes,no yes yes yes" then it will not work correctly.
To avoid this, for example, you can add some character to the end of the input string.
select (len(@string+'!')-len(replace(@string+'!',@rep,'')))/len(@rep)

[@LearnatKnowstar]

Thank you for mentioning it 👍

[@piyushjoshi8038]

Nice approach by counting length
And I think in Oracle, there is inbuilt function called regexp_count for this.

[@hv0032113]

Very relevant queries. Thanks a lot!

[@LearnatKnowstar]

Glad it was helpful!

[@nikolaybaranov2213]

A simpler solution is using STRING_SPLIT:
SELECT value, count (*) FROM STRING_SPLIT(@string, ',')
GROUP BY value
It's available from SQL Server 2016

[@sundarrajan211]

Keep upload sql videos sister

[@kingstonxavier]

Awesome trick!

[@LearnatKnowstar]

Thank you

[@whitecrowuk575]

Select count(value) from String_split(@string, ‘,’) where value =‘yes’

[@gurupradeep9648]

your voice is as same as crackconcept channel girl

[@subhadarshisethy1680]

Ty mam

[@LearnatKnowstar]

Glad that it was useful!

[@user-lk7zt8cp2g]

Hi, please type how to split first and second name from string and make first letter in first and last name in capital.
goodmorning-Good Morning

[@LearnatKnowstar]

Hello,
You can use the CharIndex or SplitString function to extract the first and last names.
See relateed video tutorials below -
ruclips.net/video/pngBhMrGAsU/видео.html
ruclips.net/video/4pdNjE98R3Q/видео.html
You can then extract first character and convert it using the uPPER function. Here is the related video -
ruclips.net/video/qQ3H-czKblY/видео.html
In some databases, there is also an initcap function that can be used.

[@user-lk7zt8cp2g]

@@LearnatKnowstar initcap(LEFT(fullname, charindex(' ', fullname) -1) as firstname, initcap(RIGTHT(fullname, (len(fullname) -fullname(" ", fullname) as last name;
It will work
? I didn't execute. Can you please verify its correct or not

[@user-lk7zt8cp2g]

Assuming that there is no seperator like space, comma can we split first name and last name from fullname,
I got this question from review section
I feel this question is impractical. Because how to split string without any seperator

[@vijaykumar]

You can execute the query without selecting the sql statements also, just execute 🙂

[@LearnatKnowstar]

Thank you