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

Подписаться 44 тыс.

Просмотров 12 тыс.

50% 1

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 - learn@knowstar.org

Опубликовано:

16 мар 2022

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 20

@jameshogge 2 года назад

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 2 года назад

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 2 года назад

Thank you for mentioning it 👍

@piyushjoshi8038 2 года назад

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

@hv0032113 2 года назад

Very relevant queries. Thanks a lot!

@LearnatKnowstar 2 года назад

Glad it was helpful!

@sundarrajan211 2 года назад

Keep upload sql videos sister

@kingstonxavier 2 года назад

Awesome trick!

@LearnatKnowstar 2 года назад

Thank you

@subhadarshisethy1680 2 года назад

Ty mam

@LearnatKnowstar 2 года назад

Glad that it was useful!

@nikolaybaranov2213 2 года назад

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

@gurupradeep9648 2 года назад

your voice is as same as crackconcept channel girl

@whitecrowuk575 Год назад

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

@user-lk7zt8cp2g 2 года назад

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 2 года назад

Hello, You can use the CharIndex or SplitString function to extract the first and last names. See relateed video tutorials below - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pngBhMrGAsU.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4pdNjE98R3Q.html You can then extract first character and convert it using the uPPER function. Here is the related video - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qQ3H-czKblY.html In some databases, there is also an initcap function that can be used.

@user-lk7zt8cp2g 2 года назад

@@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 2 года назад

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