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
LVL 1
rgb192Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ephraim WangoyaConnect With a Mentor Commented:

The search string needs to be quoted, 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

Open in new window

0
 
Neil RussellTechnical Development LeadCommented:
Easy just

SELECT *
FROM tablename
WHERE field1 like '%value%'
AND field2 like '%value%'
AND field3 like '%value%'
AND field4 like '%value%'
AND field5 like '%value%'
0
 
jawa29Commented:
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

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

Open in new window

0
 
wdosanjosCommented:
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)

Open in new window

0
 
Neil RussellTechnical Development LeadCommented:
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.
0
 
Ephraim WangoyaCommented:
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

Open in new window

0
 
rgb192Author Commented:
my existing table is named 'sitedata'

I do not want to create a new table

I want to search table 'sitedata'
0
 
Ephraim WangoyaCommented:

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

Open in new window

0
 
rgb192Author Commented:



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)

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
0
 
SharathData EngineerCommented:
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

Open in new window

0
 
rgb192Author Commented:
[alternatecolors]
[pyxispackages]
[shippingtype]
[sunpackages]
 
SELECT * FROM sitedata  WHERE [alternatecolors] LIKE %Bon%  AND [pyxispackages] LIKE %Bon%  AND [shippingtype] LIKE %Bon%  AND [sunpackages] LIKE %Bon%
0
 
SharathData EngineerCommented:
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

Open in new window

0
 
rgb192Author Commented:
[alternatecolors]
[pyxispackages]
[shippingtype]
[sunpackages]
 
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Bon'.
0
 
rgb192Author Commented:
no results

I changed
SET @Search = '''%n%'''

and there are 'n' in the table
0
 
Ephraim WangoyaCommented:

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
0
 
rgb192Author Commented:
tried with another table and it works
0
 
rgb192Author Commented:
in table 'sitedata' the fields where 'text'  so there were no results returned
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.