Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

select * from table where all fields like '%value%'

Posted on 2011-03-11
18
Medium Priority
?
796 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +4
18 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 35111574
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
 
LVL 8

Expert Comment

by:jawa29
ID: 35111671
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
 
LVL 10

Expert Comment

by:Mez4343
ID: 35111698
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 23

Expert Comment

by:wdosanjos
ID: 35111831
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 35111975
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35119298
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
 

Author Comment

by:rgb192
ID: 35152606
my existing table is named 'sitedata'

I do not want to create a new table

I want to search table 'sitedata'
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35152691

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
 

Author Comment

by:rgb192
ID: 35259573



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
 
LVL 41

Expert Comment

by:Sharath
ID: 35263641
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
 

Author Comment

by:rgb192
ID: 35298545
[alternatecolors]
[pyxispackages]
[shippingtype]
[sunpackages]
 
SELECT * FROM sitedata  WHERE [alternatecolors] LIKE %Bon%  AND [pyxispackages] LIKE %Bon%  AND [shippingtype] LIKE %Bon%  AND [sunpackages] LIKE %Bon%
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35300783
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
 

Author Comment

by:rgb192
ID: 35301626
[alternatecolors]
[pyxispackages]
[shippingtype]
[sunpackages]
 
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Bon'.
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35301769

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
 

Author Comment

by:rgb192
ID: 35310044
no results

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

and there are 'n' in the table
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35311132

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
 

Author Closing Comment

by:rgb192
ID: 35311731
tried with another table and it works
0
 

Author Comment

by:rgb192
ID: 35311733
in table 'sitedata' the fields where 'text'  so there were no results returned
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question