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.
Hi venkat your blog helped me a ton, i have a query that there is an employee table of empid, empname ,supervisor id
ReplyDeletehow can i get empid and supervisor name
This can be acheived by using self join, that is joining the table with same table. Consider we have tblEmployee as show below
DeleteEmpID 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
Above Query is wrong . Correct answer is
DeleteSelect t1.EmpID, t2.EmpName SupervisorName from tblEmployee t1 join tblEmployee t2
On t1.SupervisorID=t2.EmpID
select RIGHT(email,LEN(email)-CHARINDEX('@',email)) as EmailDomain,count(*) total
ReplyDeletefrom tablename
group by RIGHT(email,LEN(email)-CHARINDEX('@',email))
order by count(*) Desc
DeleteSelect "DOMAIN",COUNT(*) AS "Toatal Count" FROM
( Select email.EMAIL,case when email.EMAIL LIKE '%aaa%' THEN 'aaa'
when email.EMAIL LIKE '%bbb%' THEN 'bbb'
ELSE 'ccc'
END as "DOMAIN"
FROM email) D
GROUP BY "DOMAIN"
select substring(email,charindex('@',email)+1len(email))
ReplyDelete,count (*)
from table_name
group by substring(email,charindex('@',email)+1len(email))
Hi Venkat,
ReplyDeleteRecently 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.
select Date , Sum(ProductSell)
DeleteFrom #salesTable
Group by Date
hii venkat iam having question
ReplyDeletea_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
select reverse(stuff(reverse(name),charindex('_',name),1,'*')) from tbl_a
DeleteSELECT STUFF(COL1,LEN(COL1)-1,1,'*') FROM #ReplaceUS
Deleteselect reverse(stuff(reverse(string1),2,1,'*')) from row1
Deleteselect stuff(reverse(string),2,1,'*')
Deleteselect reverse(stuff(REVERSE(names),charindex('_',REVERSE(names)),1,'*')) from tablename;
Deletehi venkat i have a question
ReplyDeletedeclare @mail varchar(30)
set @mail ='jagadish873872@gmail.com'
i want print "873872@gmail.com" By using string functions,please help me to do it?
DeleteSELECT SUBSTRING(@mail,PATINDEX('%[0-9]%', @mail),LEN(@mail))
Select Substring (@mail , patindex ('%[0-9]%' , @mail) , Charindex ('@' , @mail) - patindex ('%[0-9]%' , @mail) )
Deletedeclare @mail varchar(200)='jagadish873872@gmail.com';
Deleteselect right(@mail,len(@mail)-patindex('%[1-9]%',@mail)+1);
select SUBSTRING(Email, CHARINDEX('8' , Email), LEN(Email)- CHARINDEX('8',Email)+1) from Emp
ReplyDeleteHi Venkat,
ReplyDeleteI have data in one of the column of the table as
S
Q
L
S
E
R
V
E
R
I want the output as 'SQL Server
Can you please tell us how we get that?
SELECT STUFF((SELECT colname FROM TableName FOR XML PATH(''), TYPE
Delete).value('.', 'NVARCHAR(MAX)')
,1,0,'')
SELECT DISTINCT (SELECT ''+COL1 FROM COL FOR XML PATH('')) AS RN FROM COL
ReplyDeleteSELECT DISTINCT SUBSTRING((SELECT ''+COL1 FROM COL FOR XML PATH('')),1,3)+' '+
SUBSTRING((SELECT ''+COL1 FROM COL FOR XML PATH('')),4,1)+
LOWER(SUBSTRING((SELECT ''+COL1 FROM COL FOR XML PATH('')),5,5)) FROM COL
Hi Team, I have a question... in a table one input column name is comments covid-19-june,h1n1-19-july how can I write a query to get only covid in one column and year in one column and month in another column
ReplyDeleteHi kranthi,
Deleteplease find the query:
SELECT SUBSTR('covid-19-june,h1n1-19-july',1,instr('covid-19-june,h1n1-19-july','-',1,1)-1) as covid,
SUBSTR('covid-19-june,h1n1-19-july',instr('covid-19-june,h1n1-19-july','-',1,1)+1) as covid from dual;
select left(c,charindex('-',c)-1) as virus,substring(c,charindex('-',c)+1,2) as year1,reverse(left(reverse(c),charindex('-',reverse(c))-1)) as month1 from c1;
DeleteCREATE TABLE email (EMAIL VARCHAR(20),
ReplyDeleteNAME VARCHAR(20));
INSERT INTO email VALUES ('amit@aaa.com','amit');
INSERT INTO email VALUES ('shalu@bbb.com','shalini');
INSERT INTO email VALUES ('amittt@ccc.com','bhaiya');
INSERT INTO email VALUES ('shaluttt@bbb.com','didi');
Select * from email;
Select "DOMAIN",COUNT(*) AS "Toatal Count" FROM
( Select email.EMAIL,case when email.EMAIL LIKE '%aaa%' THEN 'aaa'
when email.EMAIL LIKE '%bbb%' THEN 'bbb'
ELSE 'ccc'
END as "DOMAIN"
FROM email) D
GROUP BY "DOMAIN"
ReplyDeleteHI KARTEEK HERE
CREATE TABLE NEW (ID VARCHAR(20),
NAME VARCHAR(60));
INSERT INTO NEW VALUES ('10','SIVA,HARI');
INSERT INTO NEW VALUES ('20','MAHI,HEMU');
INSERT INTO NEW VALUES ('30','SIVA,MAHI');
INSERT INTO NEW VALUES ('40','HARI,MAHI');
I WAS EXCEPTING OUT PUT AS SIVA,MAHI,HARI,HEMU FOR THIS OUT WITCH STRING WE CAN USE
select STUFF(
Delete(SELECT ',' + NAME
from (STRING_SPLIT(NAME, ',')NAME from #NEW)
FOR XML PATH('')
), 1, 0, '') AS SingleWord
Hi All,
ReplyDeleteI have recently attended interview to Harman company and they have asked the query like below: Can anyone help me?
query1:
Table: A
Id Name Salary
1 Emp1 100
2 Emp2 200
3 Emp3 300
4 Emp4 200
5 Emp5 50
They need output like :
ID Name Salary output
1 Emp1 100
2 Emp2 200 More than previous
3 Emp3 300 More than previous
4 Emp4 200 Less than previous
5 Emp5 50 Less than previous