Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
May 19, 2000

SELECT Using LIKE

Question:
Can I use LIKE across all columns in a single table without having to name each column specifically?

Answer:
You cannot do this as a direct query against a table, but you could create a view that concatenates the values in the columns and then query that view. If you have a table like this:

TestTable
Col1 char(10)
Col2 char(10)
Col3 char(10)
And it has these values:
Col1 Col2 Col3
a    test b
test c    d
e    f    g
You can create a view called TestView that combines columns 1, 2, and 3:
if exists (select * from sysobjects where id = 
object_id(N'[dbo].[TestView]') and
OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[TestView] GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE VIEW dbo.TestView AS SELECT Col1 + Col2 + Col3 AS testcol FROM TestTable GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO
Then you can query the view like this:
select * from testview where testcol like '%test%'
And it will return this:
testcol                        
------------------------------ 
a         test      b         
test      c         d
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date