sql-serverselectcharindex

SQL - CHARINDEX, ltrim rtim

I'm trying to understand what this query is doing (SQL Server):

Select StudentId, left(fullname,charindex(' ',fullname + ' ')) as mysteryCol
From (select StudentId, ltrim (rtrim (fullname)) as fullname from STUDENTS) as S

And the query:

Select top 10 msyteryCol , count(*) as howMany from
(Select left (fullname, charindex (' ', fullname + ' ')) as mysteryCol
From (select StudentId, ltrim (rtrim (fullname)) as fullname from STUDENTS) as S) as Z
Group by mysteryCol
Having count (*) > 100
Order by 2 desc

I only understood that the charindex will find the index place of an empty space ' ' from the fullname, but I haven't really understood what is the final output of this.

Thanks for all the helpers

Solution

  • Short answer : It will read the first name From a full name. The second query will just group based on first name and give first 10 names and count ordered in descending order that have a occurrence of more than 100.

    Explanation : From (select StudentId, ltrim (rtrim (fullname)) as fullname from STUDENTS this line of code removes any leading and trailing spaces in the fullname. The only spaces left in name are now left between the first and last names(If any).

    charindex(' ',fullname + ' ') This line gets the index of the first space that occurs in the full name. If the full name is only made up of one string fullname + ' ' takes care of that. This gives us the index of the first space that occurs in the name. left(fullname,charindex(' ',fullname + ' ')) gets the string value to the left of first occurrence of a space character. Hence the first name.

    Select top 10 msyteryCol , count(*) as howMany from
(Select left (fullname, charindex (' ', fullname + ' ')) as mysteryCol
From (select StudentId, ltrim (rtrim (fullname)) as fullname from STUDENTS) as 
S) as Z
Group by mysteryCol
Having count (*) > 100
Order by 2 desc

    This query groups the query by First name and counts the number of occurrences of each first name. It displays the top 10 names that have the most counts and where count of occurrences is greater than 100.