SQL to Count Occurrence of a Character/Word in a String

In this video we will discuss how to get the number of Occurrence of a particular character or word in a string.

Most Asked Join Based Interview Question:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

create table strings (name varchar(50));
delete from strings;
insert into strings values ('Ankit Bansal'),('Ram Kumar Verma'),('Akshay Kumar Ak k'),('Rahul');

#sql #dataengineer

[anupampurkait]

Thanks Ankit for this helpful video.

However, to find no. of words, I just added 1 to the no. of spaces in each string (provided that words are separated with space only):

select name, length(name) - length(replace(name, ' ', '')) + 1 as word_count from strings;

[abhishek_grd]

Completed your 1st PlayList SQL Tips and Trics 4th Times now ! Waiting for more in this list.

[adityakaushik]

Thanks Ankit!
In one of the interview, same question was asked by interviewer. 😀

[shubhamsharma-neke]

Your channel is amazing. Best SQL content on YouTube. I have Amazon BIE 2 SQL interview on Monday. I hope I can clear it.

[shubhammajhi]

Hi sir and everyone,

I am stuck with a query:

suppose we have a transactions table with (timestamp, user_id, sender_id, amount) fields

how to pick those transactions that happen within an hour by a particular user
(suppose a user starts 1st trans at 8am, 2nd at 8.25am, 3rd at 9.05am, 4th at 9.50am, 5th at 10.45am...
so all of these records must be picked starting from 8am to 10.45am)

can anyone please suggest an approach to solve this... 🙏

[tupaiadhikari]

Great Video Ankit. May I know which software do you use to record videos? Is it a paid or a free version and if paid, than what is the subscription price of the video recording software?

[bhavinsutaria]

sir your videos now creating a new hope for me a passing interview..i give more then 8 interview but not succeed.
thanks you sir..

[taniyasaini]

Good question! Can you please make a video on Regex in SQL?

[pratikthole]

Select Length(String) - Length(Replace(String, “No of Spaces- According to char/Word Length”)) as Count from Table

[jdisunil]

I put in some time on this one and could generate this one which works with both spaces, chars and words

select name, (Datalength(name)-Datalength(replace(name, 'Ak', '')))/(Datalength('Al')) from strings

[Buzzingfact]

Nice video, but why you have degraded your video quality?

[utkarshchoubey]

create table strings(name_string varchar(30));
insert into strings values("Ankit Bansal"),
Choubey"),
Damodar Das Modi"),
Ambani"),
Venkata Sai Laxman"),
("Shubham"),
("Mahendra Singh Dhoni"),
("Sanjay Ajay Vijay Parajay");



select * from strings;
-- Q __ find occurence of character or word in a string

select name_string,
length(name_string) as str_len,
replace(name_string, " ", "") as rep_name_string,
length(replace(name_string, " ", "")) as len_without_spaces,
length(name_string) - length(replace(name_string, " ", "")) as no_of_spaces,
length(name_string) - length(replace(name_string, " ", "")) + 1 as no_of_words,
length(name_string) - length(replace(name_string, "jay", "")) as wrong_no_of_chars,
cast((length(name_string) - length(replace(name_string, "jay", "")))/length("jay") as signed) as right_no_of_chars
from strings;