Select row where any column contains a search string

In this post, I will explain SQL Query to Select row where any column contains a search string with a below example.

Below example, we are trying to select rows from an Employee_Master table where any column of a table contains the same text as TextToSearch.

1. QUERY TO CREATE TABLE

CREATE TABLE Employee_Master(

FirstName varchar(50),

MiddleName varchar(50),

LastName varchar(50)

)

GO

2. QUERY TO  INSERT DATA

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'XYZ', N'ABC', N'DEF')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'ABC', N'XYZ', N'DEF')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'WSD', N'EEE', N'FFF')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'AAA', N'KKKK', N'XYZ')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'WSD', N'XYZ', N'WER')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'AAA', N'', NULL)

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'', N'AAA', N'')

GO

3. QUERY TO SEARCH STRING IN ANY COLUMN

DECLARE @SqlQuery NVARCHAR(4000)

DECLARE @ColumnList NVARCHAR(4000)

DECLARE @TextToSearch NVARCHAR(200)

DECLARE @TableName NVARCHAR(200)

SET @TableName = 'Employee_Master'

SET @TextToSearch = XYZ

SET @ColumnList =

(

SELECT LEFT(Column_Name, LEN(Column_Name) - 1)

FROM ( SELECT Column_Name + ', '

FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @tableName

FOR XML PATH ('')

) CN (Column_Name )

)

SET @SqlQuery = 'SELECT * FROM EMPLOYEE_MASTER WHERE ''' + @TextToSearch + '''  IN (' + @ColumnList + ')';  

EXEC SP_EXECUTESQL @SqlQuery

GO

Read More

SQL Query to find nth Lowest Salary

In this post, I will explain a SQL Query to find nth Lowest salary. 

In below query subquery returns the count of a distinct salary lower than current employee salary. Then the result of subquery will be compared with @N and record with subquery result equal @N will be returned as result.

DECLARE @N AS int = 2

SELECT * FROM Employee_Master EM1
WHERE @N  = (SELECT COUNT(DISTINCT(EM2.Salary)) FROM Employee_Master EM2 WHERE EM2.Salary < EM1.SALARY)

Table Script-

CREATE TABLE Employee_Master(
EmployeeId int IDENTITY(1,1) ,
EmployeeName varchar(100) NULL,
Salary decimal(18, 2) NULL,
) 

Read More

SQL Query to find nth Highest salary

In this post, I will explain a SQL Query to find nth Highest salary. 

In below query subquery returns the count of a distinct salary greater than current employee salary. Then the result of subquery will be compared with @N and record with subquery result equal @N will be returned as result.

DECLARE @N AS int = 2

SELECT * FROM Employee_Master EM1
WHERE @N  = (SELECT COUNT(DISTINCT(EM2.Salary)) FROM Employee_Master EM2 WHERE EM2.Salary > EM1.SALARY)

Table Script-

CREATE TABLE Employee_Master(
EmployeeId int IDENTITY(1,1) ,
EmployeeName varchar(100) NULL,
Salary decimal(18, 2) NULL,
) 

Read More

SQL Like In Query (Like With In operator)

In this post, I will explain How to write SQL Like In Query (Like With In operator) with a below example.

Below example, we are trying to select rows from an Employee_Master table where any column of a table contains a text like the text to Search. For that, we should have where clause with Text Like TextToSearch In Emplyee_Master column list. But with SQL can not write such where clause. So to do the same thing we are using Like with Or operator.

1. QUERY TO CREATE TABLE

CREATE TABLE Employee_Master(

            FirstName varchar(50),

            MiddleName varchar(50),

            LastName varchar(50)

)

GO

2. QUERY TO  INSERT DATA

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'XYZ', N'ABC', N'DEF')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'ABC', N'XYZ', N'DEF')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'WSD', N'EEE', N'FFF')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'AAA', N'KKKK', N'XYZ')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'WSD', N'XYZ', N'WER')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'AAA', N'', NULL)

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'', N'AAA', N'')

GO

3. QUERY TO SEARCH STRING IN ANY COLUMN

DECLARE @SqlQuery NVARCHAR(4000)

DECLARE @ColumnList NVARCHAR(4000)

DECLARE @TextToSearch NVARCHAR(200)

DECLARE @TableName NVARCHAR(200)

SET @TableName = 'Employee_Master'

SET @TextToSearch = 'XY'

SET @ColumnList = 

                                    (

                                    SELECT LEFT(Column_Name, LEN(Column_Name) - 2)

                                    FROM ( SELECT Column_Name + ' LIKE ''%' + @TextToSearch +'%''' + ' OR '

                                                            FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @tableName

                                                            FOR XML PATH ('')

                                                ) CN (Column_Name )

                                    )

SET @SqlQuery = 'SELECT * FROM EMPLOYEE_MASTER WHERE ' + @ColumnList ;  

PRINT @SqlQuery

EXEC SP_EXECUTESQL @SqlQuery

GO

Read More

How to find second highest salary in SQL Server?

In this post, I will explain How to find second highest salary in SQL Server table?

Find the second highest salary is a common interview question for many peoples. There are different ways to get a second highest salary using SQL Query. Here I will explain a few of them with below example.

Example-

Consider below simple table Employee.

Name	Salary
A	100,000.00
B	1,000,000.00
C	40,000.00
D	500,000.00

In the above table, D has the second highest salary. Finding the highest salary using SQL query is very easy.
SELECT MAX(Salary) as salary FROM Employee

We can nest the above query to find the second highest salary.

SELECT MAX(Salary) AS salary
FROM Employee

WHERE Salary < (SELECT MAX(Salary) FROM Employee)

Below is another way to find second highest salary.

SELECT  Salary
FROM    Employee
ORDER BY Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY

Read More