Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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

Suggested Videos:
Part 11 - Real time example for right join
Part 12 - Can we join two tables without primary foreign key relation
Part 13 - Difference between blocking and deadlocking

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.



We will use the following Students table for this example
sql query without using like operator



SQL Script to create the Students table

Create table Students
(
     ID int primary key identity,
     Name nvarchar(50),
     Gender nvarchar(50),
     Salary int
)
Go

Insert into Students values ('Mark', 'Male', 60000)
Insert into Students values ('Steve', 'Male', 45000)
Insert into Students values ('James', 'Male', 70000)
Insert into Students values ('Mike', 'Male', 45000)
Insert into Students values ('Mary', 'Female', 30000)
Insert into Students values ('Valarie', 'Female', 35000)
Insert into Students values ('John', 'Male', 80000)
Go

Interview question : Write a query to select all student rows whose Name starts with letter 'M' without using the LIKE operator

The output should be as shown below
sql search without like wildcard

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'

sql server interview questions and answers

1 comment:

  1. I also want to point two other methods which will does the same function.

    SELECT * FROM Students WHERE Name between 'M' and 'N'

    Thanks kudvenkat for such such informative videos.
    SELECT * FROM Students WHERE Name >= 'M' and Name < 'N'

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.