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
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
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