name

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



Sharing buttons:

this is part 14 of sequence of interview

questions and answers video series in

this video we'll discuss writing a

select query to retrieve all student

names that starts with letter M without

using the like operator so here is what

we want to do we're going to make use of

this students table in this example

based on this table we want to write a

select query that returns us all these

student names that start with letter M

look at the name column within the

results all the names start with letter

M if the interviewer has not mentioned

not to use the like operator then we

would have very easily written this

query using the like operator so our

query would have been like this select

star from students wear name like M

percentage so this is going to return us

all the student names that start with

letter M but the interviewer has

specifically told us not to use the like

operator in sequel server there are

three inbuilt functions that we can use

to achieve exactly the same thing we can

use character index function or left or

substring functions let's look at using

one by one first let's use the character

index function select star from students

where character index off now we want to

find the index of character M in the

name column and if the index of

character M within the name column is

one then that means M is the first

character within the name so we want all

such names so when we execute this query

notice we get all the names that start

with letter M now let's look at using

the left function so let's make a copy

of this query and instead of using care

index function let's use the left

function so from the name column I want

the first character from the left side

and if that is equal to M that means

those names start with letter M so we

want all such names again when we

execute this query notice we get all the

names that start with letter M finally

let's look at using substring function

select style from students where

substring of so we want a substring from

the name column and we want to start at

character position 1 and we want only

one character if that is again equal to

M then we know the name starts with

letter M so when we execute this again

we get all the names that start with

letter M so here are those three queries

that we just looked at thank you for

listening and have a great day