Showing posts with label Any Column. Show all posts
Showing posts with label Any Column. Show all posts

Tuesday 19 February 2019

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

Thursday 28 June 2018

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