HomeНаука и техникаRelated VideosMore From: kudvenkat

Sql query to select all names that start with a given letter without like operator

316 ratings | 59706 views
Text Article http://csharp-video-tutorials.blogspot.com/2017/01/sql-query-to-select-all-names-that.html Slides http://csharp-video-tutorials.blogspot.com/2017/01/sql-query-to-select-all-names-that_31.html SQL Server Interview Questions and Answers text articles & slides http://csharp-video-tutorials.blogspot.com/2014/05/sql-server-interview-questions-and.html SQL Server Interview Questions and Answers playlist https://www.youtube.com/playlist?list=PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss writing a SQL query to retrieve all student names that start with letter 'M' without using the LIKE operator. If the interviewer has not mentioned not to use LIKE operator, we would have written the query using the LIKE operator as shown below. SELECT * FROM Students WHERE Name LIKE 'M%' We can use any one of the following 3 SQL Server functions, to achieve exactly the same thing. CHARINDEX LEFT SUBSTRING The following 3 queries retrieve all student rows whose Name starts with letter 'M'. Notice none of the queries are using the LIKE operator. SELECT * FROM Students WHERE CHARINDEX('M',Name) = 1 SELECT * FROM Students WHERE LEFT(Name, 1) = 'M' SELECT * FROM Students WHERE SUBSTRING(Name, 1, 1) = 'M'
Html code for embedding videos on your blog
Text Comments (30)
Yash Gaikwad (1 month ago)
Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name. Find the country that has all the vowels and no spaces in its name. How to select an object that has all the characters we want? Please help sir
Vindhyachal Gupta (3 months ago)
Anime Blaster (1 year ago)
Very niceee video but i donot know what is the full name of sql
kudvenkat (1 year ago)
SQL stands for Structured Query Language. Hope this answers your question. I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit http://www.pragimtech.com/order.aspx Slides and Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. https://www.youtube.com/watch?v=y780MwhY70s If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=kudvenkat If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. Good Luck Venkat
Deekshith Raj (1 year ago)
i want to get all values which have seme value Example: any of the two have same name i want to get those values
Ibrahim Noor (1 year ago)
Thank You a lot, you are awsm
MichaStg29 (1 year ago)
really good tutorials for ADO.NET work space...
Anuj Kumar (1 year ago)
Hello SIr, I have a query that i want rewrite without using like operator. Could you please help me? The query is: SELECT * FROM Employee WHERE EmpName LIKE '%an%' and let me know is it possible without LIKE or not?
Anuj Kumar (1 year ago)
I hadn't see your response so that i am responding right now. Output is different in each cases.
Dev Expost (1 year ago)
Anuj, Just a guess... SELECT * FROM employee WHERE CHARINDEX("n", EmpName) > 1 AND (CHARINDEX("n", EmpName) - CHARINDEX("a", EmpName)) = 1;
Umesh P (1 year ago)
I also want to point two other methods which will does the same function. SELECT * FROM Students WHERE Name between 'M' and 'N' SELECT * FROM Students WHERE Name >= 'M' and Name < 'N' Thanks kudvenkat for such such informative videos.
Johnny Depp (2 years ago)
Very nice video.. Thank you very much
Vagelis Prokopiou (2 years ago)
Thanx sir. Very nice video.
ANIL KUMAR ANANDAN (2 years ago)
Venkat Sir. This morning i learnt Something new :) Happy!
Jeewan Maharjan (2 years ago)
Welcome back sir. Good to see u in 2017.
Deep State Actor (2 years ago)
Thanks for another SQL video.
Mr Irrepressible (2 years ago)
what is the quickest method of the 3?
Rivanni (2 years ago)
IrrepressibleGuile Mr Irrepressible: Of the 4. Also comparing the LIKE option.
Muhammad Rehbar Sheikh (2 years ago)
thanks venkat sir... after long time u uploaded a video... please keep teaching us.
Selman Ekici (2 years ago)
I am writing to thank you specifically for this comment. I started watching your Youtube channel for to be software developer one and a half years ago And nowadays,I have been working for fifteen months as a software developer. I am not the best developer ever seen but I am not bad, everything going well. I need time to develop myself. I thank you all sincerely for sharing your experience and knowledge with us. thanks for everything all your shared. I will continue to watch and follow you
Selman Ekici (2 years ago)
Shantilal Suthar I will
Shantilal Suthar (2 years ago)
Selman Erhan Ekici do not quit....keep learning..:-)
Mario G. Cardiel (2 years ago)
How did the query that used the SUBSTRING function work? It should have said "... SUBSTRING(Name, 1, 1) = 'M'", and not "... SUBSTRING(Name, 1, 1) - 'M'".
Mario G. Cardiel (2 years ago)
Yeah, I can easily see *that* work, so it would not be that interesting.
Mario G :) If you are really interested to do so then use this logic .....where ASCII(SUBSTRING(name,1,1))-ASCII('M')=0;
Johnny Depp (2 years ago)
kudvenkat it's ok, you don't need to be sorry.
Mario G. Cardiel (2 years ago)
Thanks. It would have been quite interesting if the "-" had actually worked!
kudvenkat (2 years ago)
It is actually SUBSTRING(Name, 1, 1) = 'M'". For some reason the video quality was lost and it appeared as - instead of =. Sorry about the confusion.
venkatesh kannan (2 years ago)
Wow, I was waiting for your videos on SQL Server for a very very long time now. You're amazing!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.