rgb192
asked on
select * from table where all fields like '%value%'
is there a way to write that query so I dont have to write it for every varchar column
or maybe make it one query so I dont need to do 5 query
or maybe make it one query so I dont need to do 5 query
Or if you want any column that contains your search then it will be.
SELECT *
FROM tablename
WHERE field1 like '%value%'
OR field2 like '%value%'
OR field3 like '%value%'
OR field4 like '%value%'
OR field5 like '%value%'
Jawa29
SELECT *
FROM tablename
WHERE field1 like '%value%'
OR field2 like '%value%'
OR field3 like '%value%'
OR field4 like '%value%'
OR field5 like '%value%'
Jawa29
Beat me to it, but maybe you need a working example and I used OR instead of AND because I assumed you wanted to return the row where any of the fields 1-5 contained your 'value'.
CREATE TABLE MyTable (Field1 varchar(max),Field2 varchar(max),
Field3 varchar(max),Field4 varchar(max),Field5 varchar(max))
INSERT INTO MyTable Values('John Doe','John Smith','Fred Sanford','Jimmy Walker','Grady');
INSERT INTO MyTable Values('Jane Doe','Alice Smith','Amy Sanford','Karen Walker','Grady');
select * from MyTable
WHERE Field1 LIKE '%John%' OR
Field2 LIKE '%John%' OR
Field3 LIKE '%John%' OR
Field4 LIKE '%John%' OR
Field5 LIKE '%John%'
DROP TABLE MyTable
You can try something like this. It dynamically generates the SELECT based on the data dictionary.
DECLARE @schema nvarchar(50)
DECLARE @tname nvarchar(50)
DECLARE @like nvarchar(50)
DECLARE @cmd nvarchar(4000)
set @schema = 'dbo'
set @tname = 'YouTable'
set @like = '%value%'
set @cmd = 'SELECT * FROM [' + @schema + '].[' + @tname + '] WHERE 1 <> 1 ' +
(SELECT (' OR [' + [COLUMN_NAME] + '] LIKE ''' + @like + '''')
FROM [INFORMATION_SCHEMA].[COLUMNS] c
WHERE c.TABLE_SCHEMA = @schema
AND c.TABLE_NAME = @tname
AND c.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
FOR XML PATH(''))
exec (@cmd)
I used AND in the example as you said "select * from table where all fields like '%value%'" i took that to mean litraly where ALL fields like.
Whether its AND or OR the concept is the same.
Whether its AND or OR the concept is the same.
You could try it this way
DECLARE @TableName varchar(128), @ColumnName varchar(128), @Search varchar(128)
DECLARE @Sql varchar(max)
SET @TableName = 'Customers'
SET @ColumnName = ''
SET @Sql = ''
SET @Search = '''Bon' + '%'''
WHILE @COLUMNNAME IS NOT NULL
BEGIN
SET @COLUMNNAME = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
AND DATA_TYPE IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR')
AND QUOTENAME(COLUMN_NAME) > @COLUMNNAME
)
IF @COLUMNNAME IS NOT NULL
BEGIN
IF @SQL = ''
SET @SQL = ' WHERE ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' '
ELSE
SET @SQL = @SQL + ' AND ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' ' --or use "OR"
END
END
IF @Sql <> ''
BEGIN
SET @Sql = 'SELECT * FROM ' + @TableName + ' ' + @Sql
EXEC(@SQL)
END
ASKER
my existing table is named 'sitedata'
I do not want to create a new table
I want to search table 'sitedata'
I do not want to create a new table
I want to search table 'sitedata'
Replace customers with your table name
DECLARE @TableName varchar(128), @ColumnName varchar(128), @Search varchar(128)
DECLARE @Sql varchar(max)
SET @TableName = 'sitedata'
SET @ColumnName = ''
SET @Sql = ''
SET @Search = '''Bon' + '%'''
WHILE @COLUMNNAME IS NOT NULL
BEGIN
SET @COLUMNNAME = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
AND DATA_TYPE IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR')
AND QUOTENAME(COLUMN_NAME) > @COLUMNNAME
)
IF @COLUMNNAME IS NOT NULL
BEGIN
IF @SQL = ''
SET @SQL = ' WHERE ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' '
ELSE
SET @SQL = @SQL + ' AND ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' ' --or use "OR"
END
END
IF @Sql <> ''
BEGIN
SET @Sql = 'SELECT * FROM ' + @TableName + ' ' + @Sql
EXEC(@SQL)
END
ASKER
DECLARE @schema nvarchar(50)
DECLARE @tname nvarchar(50)
DECLARE @like nvarchar(50)
DECLARE @cmd nvarchar(4000)
set @schema = 'dbo'
set @tname = 'YouTable'
set @like = '%value%'
set @cmd = 'SELECT * FROM [' + @schema + '].[' + @tname + '] WHERE 1 <> 1 ' +
(SELECT (' OR [' + [COLUMN_NAME] + '] LIKE ''' + @like + '''')
FROM [INFORMATION_SCHEMA].[COLU
WHERE c.TABLE_SCHEMA = @schema
AND c.TABLE_NAME = @tname
AND c.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
FOR XML PATH(''))
exec (@cmd)
changing tname to sitedata, value to search term this query returns nothing
DECLARE @TableName varchar(128), @ColumnName varchar(128), @Search varchar(128)
DECLARE @Sql varchar(max)
SET @TableName = 'sitedata'
SET @ColumnName = ''
SET @Sql = ''
SET @Search = '''Bon' + '%'''
WHILE @COLUMNNAME IS NOT NULL
BEGIN
SET @COLUMNNAME = (
SELECT MIN(QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
AND DATA_TYPE IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR')
AND QUOTENAME(COLUMN_NAME) > @COLUMNNAME
)
IF @COLUMNNAME IS NOT NULL
BEGIN
IF @SQL = ''
SET @SQL = ' WHERE ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' '
ELSE
SET @SQL = @SQL + ' AND ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' ' --or use "OR"
END
END
IF @Sql <> ''
BEGIN
SET @Sql = 'SELECT * FROM ' + @TableName + ' ' + @Sql
EXEC(@SQL)
END
changing Bon to searchterm
this query always returns nothing
Do you really have the character columns in sitedate table? Try printing the sql and see.
DECLARE @TableName varchar(128), @ColumnName varchar(128), @Search varchar(128)
DECLARE @Sql varchar(max)
SET @TableName = 'sitedata'
SET @ColumnName = ''
SET @Sql = ''
SET @Search = '%Bon%'
WHILE @COLUMNNAME IS NOT NULL
BEGIN
SET @COLUMNNAME = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
AND DATA_TYPE IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR')
AND QUOTENAME(COLUMN_NAME) > @COLUMNNAME
)
print @ColumnName
IF @COLUMNNAME IS NOT NULL
BEGIN
IF @SQL = ''
SET @SQL = ' WHERE ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' '
ELSE
SET @SQL = @SQL + ' AND ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' ' --or use "OR"
END
END
IF @Sql <> ''
BEGIN
SET @Sql = 'SELECT * FROM ' + @TableName + ' ' + @Sql
print(@SQL)
END
ASKER
[alternatecolors]
[pyxispackages]
[shippingtype]
[sunpackages]
SELECT * FROM sitedata WHERE [alternatecolors] LIKE %Bon% AND [pyxispackages] LIKE %Bon% AND [shippingtype] LIKE %Bon% AND [sunpackages] LIKE %Bon%
[pyxispackages]
[shippingtype]
[sunpackages]
SELECT * FROM sitedata WHERE [alternatecolors] LIKE %Bon% AND [pyxispackages] LIKE %Bon% AND [shippingtype] LIKE %Bon% AND [sunpackages] LIKE %Bon%
I think you need OR instead of AND. try this.
DECLARE @TableName varchar(128), @ColumnName varchar(128), @Search varchar(128)
DECLARE @Sql varchar(max)
SET @TableName = 'sitedata'
SET @ColumnName = ''
SET @Sql = ''
SET @Search = '%Bon%'
WHILE @COLUMNNAME IS NOT NULL
BEGIN
SET @COLUMNNAME = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
AND DATA_TYPE IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR')
AND QUOTENAME(COLUMN_NAME) > @COLUMNNAME
)
print @ColumnName
IF @COLUMNNAME IS NOT NULL
BEGIN
IF @SQL = ''
SET @SQL = ' WHERE ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' '
ELSE
SET @SQL = @SQL + ' OR ' + @COLUMNNAME + ' LIKE ' + @SEARCH + ' ' --or use "OR"
END
END
IF @Sql <> ''
BEGIN
SET @Sql = 'SELECT * FROM ' + @TableName + ' ' + @Sql
EXEC(@SQL)
END
ASKER
[alternatecolors]
[pyxispackages]
[shippingtype]
[sunpackages]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Bon'.
[pyxispackages]
[shippingtype]
[sunpackages]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Bon'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
no results
I changed
SET @Search = '''%n%'''
and there are 'n' in the table
I changed
SET @Search = '''%n%'''
and there are 'n' in the table
If you are using the "AND" operator, then all your character fields must satisfy the condition otherwise you will get no results back
If using "OR" yo definitely should be getting results if your search string exists in any of the character fields
ASKER
tried with another table and it works
ASKER
in table 'sitedata' the fields where 'text' so there were no results returned
SELECT *
FROM tablename
WHERE field1 like '%value%'
AND field2 like '%value%'
AND field3 like '%value%'
AND field4 like '%value%'
AND field5 like '%value%'