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