SQL Server interview questions on string manipulation functions

The following 2 SQL Server Interview questions were asked when I attended an interview for SQL Server Developer role.

Can you list a few useful string manipulation functions in SQL Server?

Then he asked me, Can you give me one example of where you have used these functions in your experience?
The following is one simple real time example, where we can use LEN(), CHARINDEX() and SUBSTRING() functions. Let us assume we have table as shown below.

I want you to write a query to find out total number of emails, by domain. The result of the query should be as shown below.

We can use LEN(), CHARINDEX() and SUBSTRING() functions to produce the desired results. Please refer to the query below.

Select SUBSTRING(Email,CHARINDEX('@',Email)+1,(LEN(Email) - CHARINDEX('@',Email))) as EmailDomain, Count(*) as Total
From TableName
Group By SUBSTRING(Email,CHARINDEX('@',Email)+1,(LEN(Email) - CHARINDEX('@',Email)))
Order by Count(*) Desc

There could be even better ways of producing the same result. If you feel you have a better way of producing the same output, please share using the form below.


  1. Hi venkat your blog helped me a ton, i have a query that there is an employee table of empid, empname ,supervisor id

    how can i get empid and supervisor name

    1. This can be acheived by using self join, that is joining the table with same table. Consider we have tblEmployee as show below

      EmpID EmpName SupervisorID
      101 Navin 104
      102 Mahesh 101
      103 Bala 101
      104 Billy NULL

      select t1.EmpID, t2.EmpName SupervisorName from tblEmployee t1 JOIN tblEmployee t2 on t1.EmpID = t2.SupervisorID

    2. Above Query is wrong . Correct answer is
      Select t1.EmpID, t2.EmpName SupervisorName from tblEmployee t1 join tblEmployee t2
      On t1.SupervisorID=t2.EmpID

  2. select RIGHT(email,LEN(email)-CHARINDEX('@',email)) as EmailDomain,count(*) total
    from tablename
    group by RIGHT(email,LEN(email)-CHARINDEX('@',email))
    order by count(*) Desc

  3. select substring(email,charindex('@',email)+1len(email))
    ,count (*)
    from table_name
    group by substring(email,charindex('@',email)+1len(email))

  4. Hi Venkat,
    Recently I gone for a interview there one my questions asked in the hiring process is Query Test, in the round they asked "A list of employees and sales done by employees, " the question is I need to display their sales details depend on the date i.e. by Quarter basis. (all four quarter of a year).
    For example:
    Date | EmpName | ProductSell
    1st Quarter | A | 3
    | B | 6
    2nd Quarter | C | 12
    Please do reply a query for it.

  5. hii venkat iam having question

    a_b_c,a_b_c_d,a_b_c_d_e these are rows in one column .how to replace last _ with * in all rows

    output: a_b*c,a_b_c*d,a_b_c_d*e

    1. select reverse(stuff(reverse(name),charindex('_',name),1,'*')) from tbl_a

    2. SELECT STUFF(COL1,LEN(COL1)-1,1,'*') FROM #ReplaceUS

  6. hi venkat i have a question

    declare @mail varchar(30)
    set @mail ='jagadish873872@gmail.com'

    i want print "873872@gmail.com" By using string functions,please help me to do it?


    1. SELECT SUBSTRING(@mail,PATINDEX('%[0-9]%', @mail),LEN(@mail))

  7. select SUBSTRING(Email, CHARINDEX('8' , Email), LEN(Email)- CHARINDEX('8',Email)+1) from Emp


If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

Disclaimer - Terms of use - Contact Us