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

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

272 ratings | 52062 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 (29)
Vindhyachal Gupta (12 days 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 (1 year ago)
Very nice video.. Thank you very much
Vagelis Prokopiou (1 year ago)
Thanx sir. Very nice video.
Venkat Sir. This morning i learnt Something new :) Happy!
Jeewan Maharjan (1 year ago)
Welcome back sir. Good to see u in 2017.
Sleve McDichael (1 year ago)
Thanks for another SQL video.
what is the quickest method of the 3?
Rivanni (1 year ago)
IrrepressibleGuile Mr Irrepressible: Of the 4. Also comparing the LIKE option.
thanks venkat sir... after long time u uploaded a video... please keep teaching us.
Selman Ekici (1 year 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 (1 year ago)
Shantilal Suthar I will
Shantilal Suthar (1 year ago)
Selman Erhan Ekici do not quit....keep learning..:-)
Mario G. Cardiel (1 year 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 (1 year 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 (1 year ago)
kudvenkat it's ok, you don't need to be sorry.
Mario G. Cardiel (1 year ago)
Thanks. It would have been quite interesting if the "-" had actually worked!
kudvenkat (1 year 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 (1 year 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.