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