Friday 17 January 2014

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?
LEN(), SUBSTRING(), CHARINDEX(), LEFT(), RIGHT() etc.



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.

No comments:

Post a Comment

C# program Selection Sorting

Selection sort is a straightforward sorting algorithm. This algorithm search for the smallest number in the elements array and then swap i...