Trying to find all last names starting with a letter-CodePudding

I'm working a project for school in T-SQL.

I have an Advisors table that is fully set up. I'm trying to update the Student table so that each StudentID is associated with an AdvisorID (referencing the Advisors table). The Student table is fully set up, minus the AdvisorID column. Both tables have Name_Full, Name_First, and Name_Last for every Advisor and Student in the respective tables.

I'm trying to find all students that have a Name_Last starting with 'R'. I know for a fact that there is at least one student that qualifies since there is a student with a Name_Last = 'Ramos'.

I tried searching for every student with a Name_Last starting with the letter 'R' using the following code.

SELECT Name_Last
    FROM Student
    WHERE Name_Last IN ('R%')

This query returns nothing. I've tried using '=' and 'LIKE' instead of 'IN' and those did not work either. I've tried using 'CONTAINS' which also didn't work. I tried:

WHERE CHARINDEX('R', Name_Last) = 1

This did not work either. Once I get this working, I'd like to be able to copy it into a WHERE clause using BETWEEN, as I want to assign an AdvisorID to students within certain ranges.

CodePudding user response：

So I figured out what was wrong.

The code was working perfectly fine. BUT every Name_Last in Student started with a ' '.

So altering the code to:

SELECT Name_Last
    FROM Student
    where Name_Last like ' R%'

Worked perfectly. Thank you for your suggestions though!

CodePudding user response：

The missing part in your statement is the name which is the first word of a full name.

Finds a string that starts with 'R'

SELECT Name_Last
    FROM Student
    WHERE Name_Last IN ('R%')

Finds a string that includes ' R'

SELECT Name_Last
    FROM Student
    WHERE Name_Last LIKE '% R%'

Hope this helps to your project.
Good luck!