SQL Query | How many times a substring occurs in a value ? | REPLACE | LEN
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- 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.c...
Email - learn@knowstar.org
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))
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)
Thank you for mentioning it 👍
Very relevant queries. Thanks a lot!
Glad it was helpful!
A simpler solution is using STRING_SPLIT:
SELECT value, count (*) FROM STRING_SPLIT(@string, ',')
GROUP BY value
It's available from SQL Server 2016
Nice approach by counting length
And I think in Oracle, there is inbuilt function called regexp_count for this.
your voice is as same as crackconcept channel girl
Select count(value) from String_split(@string, ‘,’) where value =‘yes’
Awesome trick!
Thank you
Keep upload sql videos sister
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
Hello,
You can use the CharIndex or SplitString function to extract the first and last names.
See relateed video tutorials below -
th-cam.com/video/pngBhMrGAsU/w-d-xo.html
th-cam.com/video/4pdNjE98R3Q/w-d-xo.html
You can then extract first character and convert it using the uPPER function. Here is the related video -
th-cam.com/video/qQ3H-czKblY/w-d-xo.html
In some databases, there is also an initcap function that can be used.
@@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
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
Ty mam
Glad that it was useful!
You can execute the query without selecting the sql statements also, just execute 🙂
Thank you