July 10, 2009 at 4:07 am
All,
Is there a simple way to do a select on a table to compare any column to see if it matches a value using SQL.
Suppose I have a table MyTable with the following data:
[font="Courier New"]Name Age Description Field1 Field2
Tom 30 Something 1 ghjk
Jen 23 Something 2 fjfor
Jim 22 Something 3 ghop
[/font]
Now when I want to do a select, I want to do something as follows:
SELECT *
FROM MyTable
WHERE Any Column LIKE 'Something%'
This would return all records.
SELECT *
FROM MyTable
WHERE Any Column LIKE '%gh%'
This would return Tom and Jim's records.
I can't think of any simple SQL to do this off top of my head. Are there any decent ways to do this?
Andez
July 10, 2009 at 5:15 am
Other than building it in dynamic sql then no...
July 10, 2009 at 5:19 am
There's no such syntax. If you want to compare to all the columns, you have to explicitly compare to all columns.
So, in this case
SELECT Name, Age, Description, Field1, Field2
FROM MyTable
WHERE Name LIKE 'Something%' OR Description LIKE 'Something%' OR Field1 LIKE 'Something%' OR Field2 LIKE 'Something%'
I left age out of the where because I assume it's numeric and doing a like comparison with a string would probably give a conversion error.
Just note that this is likely to perform poorly on larger tables because it will very likely table scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2009 at 5:37 am
I'm curious as to the business case for doing this. It sounds to me like you need to have your data normalized better.;-)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 10, 2009 at 5:51 am
Hey all,
Nothing to do with business, more diagnostics. Just wanted something quick and dirty - I guess a lazy way of checking.
Suppose a situation where you have a record in one table column, with a value of fred. Now you know that data is lurking somewhere in another table (that will have fred as a value somewhere). Like a third party database someone else has written 🙁
Now suppose you identify the table but there are lots of columns to find 'fred' in.
Instead of typing WHERE Column1 = 'fred' OR Column2 = 'fred' and so on...
I'd thought about dynamic script first but then I've had the odd bit of SQL that was "new to me" of late so wondered if there was some part of the SQL syntax I may have overlooked.
Andez
July 10, 2009 at 6:41 am
I'd seen a similar requirement before, so I created this procedure...you have to pass it two parameters: the tablename and the search term.
it uses dynamic sql to quiery just the char/varchar type columns for the search term:
ALTER PROCEDURE TABLEVIEWSEARCH @TABLENAME VARCHAR(60),@SEARCHSTRING VARCHAR(50)
-- EXEC TABLEVIEWSEARCH 'GMACT','demo'
-- EXEC TABLEVIEWSEARCH 'TABLEORVIEW','TEST'
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(500),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #TMPCOLLECTION
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.NAME = @TABLENAME
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT COLNAME FROM #TMPCOLLECTION ORDER BY COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @COLUMNNAME
WHILE @@FETCH_STATUS -1
BEGIN
--SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
SET @SQL = 'IF EXISTS(SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''''%' + @SEARCHSTRING + '%'''''') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
GO
ALTER PROCEDURE TABLEVIEWSEARCH2 @TABLENAME VARCHAR(60),@SEARCHSTRING VARCHAR(50)
-- EXEC TABLEVIEWSEARCH2 'GMACT','SOURCE'
-- EXEC TABLEVIEWSEARCH2 'TABLEORVIEW','TEST'
AS
BEGIN
SET NOCOUNT ON
DECLARE @FINALSQL VARCHAR(MAX),
@COLUMNNAMES VARCHAR(MAX)
SET @FINALSQL = 'SELECT * FROM [' + @TABLENAME + '] WHERE 1 = 2 '
SELECT
@FINALSQL = @FINALSQL + ' OR [' + SYSCOLUMNS.NAME + '] LIKE ''%' + @SEARCHSTRING + '%'' '
FROM SYSCOLUMNS
WHERE OBJECT_NAME(id) = @TABLENAME
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY COLID
PRINT @FINALSQL
EXEC(@FINALSQL)
END --PROC
Lowell
July 10, 2009 at 7:01 am
create table dbo.tmpTestTable(a nvarchar(50),b nvarchar(10),c int,d float)
Insert into dbo.tmpTestTable select 'a1f','2a',3,4
Insert into dbo.tmpTestTable select 'a2ff','a1',3,4
Insert into dbo.tmpTestTable select 'a3fff','a2',1,4
Insert into dbo.tmpTestTable select 'a4fff','2a',3,1
Insert into dbo.tmpTestTable select 'a5fff','a2',3,4
select * from dbo.tmpTestTable
declare @Search nvarchar(50)
set @Search='4'
declare @sql nvarchar(max)
--run through all the columns
select @sql=coalesce(@sql+'or ['+[name]+'] like ''%'+@Search+'%'' ',' ['+[name]+'] like ''%'+@Search+'%'' ')
from sys.columns where object_id=Object_id('[dbo].[tmpTestTable]') --you can filter on certain types
select @sql='Select * from [dbo].[tmpTestTable] where '+@sql
print @sql
exec sp_executesql @sql
Personally I would search for each column separate! Then you can add the results separate and add which column is reponsible.
January 5, 2010 at 4:01 am
I think you have already several good answers, or at last the kind I would have suggested before this one. Despite of this, and only as a alternative to consider, you could concatenate the string fields before LIKE comparison (you'll can't expect high performance, I guess):
Select *
From Table
Where col1 + col2 + col3 like '%' + 'SearchedString' + '%'
January 5, 2010 at 4:16 am
dbuendiab (1/5/2010)
I think you have already several good answers, or at last the kind I would have suggested before this one. Despite of this, and only as a alternative to consider, you could concatenate the string fields before LIKE comparison (you'll can't expect high performance, I guess):Select *
From Table
Where col1 + col2 + col3 like '%' + 'SearchedString' + '%'
You need to make sure to separate each column. Otherwise you might get wrong results.
DECLARE @t TABLE (col1 varchar(10), col2 varchar(10) ,col3 varchar(10))
INSERT INTO @t values('book shelf','red','Mike')
Select *
From @t
Where col1 + col2 + col3 like '%' + 'fred' + '%'
-- versus
Select *
From @t
Where col1 + '_' + col2 + '_' + col3 like '%' + 'red' + '%'
January 5, 2010 at 7:43 am
lmu92 (1/5/2010)
dbuendiab (1/5/2010)
I think you have already several good answers, or at last the kind I would have suggested before this one. Despite of this, and only as a alternative to consider, you could concatenate the string fields before LIKE comparison (you'll can't expect high performance, I guess):Select *
From Table
Where col1 + col2 + col3 like '%' + 'SearchedString' + '%'
You need to make sure to separate each column. Otherwise you might get wrong results.
DECLARE @t TABLE (col1 varchar(10), col2 varchar(10) ,col3 varchar(10))INSERT INTO @t values('book shelf','red','Mike')
Select *
From @t
Where col1 + col2 + col3 like '%' + 'fred' + '%'
-- versus
Select *
From @t
Where col1 + '_' + col2 + '_' + col3 like '%' + 'red' + '%'
I agree, the proposed query depends on the data, but if you are cautious on it it can be a 'quick & dirty' approach.
April 12, 2012 at 10:50 am
Great idea, but you either need to handle nulls ... isnull({{column}},'') or cast them as strings... Found this out the hard way.
April 12, 2012 at 11:48 am
If you want to test equality conditions, you can do this:
Where @Parameter in (Col1, Col2, Col3)
That will find any column that is equal to the parameter value.
Can't do that so easily with Like instead of equality.
If it's a whole word, not a word fragment, you can find this kind of thing with full text indexing. Like "Fred" in "Fred and Wilma", not like "bam" in "Bambam".
Edit: And you'll definitely want to use IsNull or something similar if you concatenate columns together. That's been mentioned, but I thought I'd just reinforce that statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2012 at 4:30 am
I know this is an old thread, but just in case someone is still interested, here's a solution without explicitly referencing all columns and no dynamic SQL.
CREATE TABLE #Test
(
Name VARCHAR(100),
Age INT,
Description VARCHAR(100),
Field1 INT,
Field2 VARCHAR(100)
)
GO
INSERT INTO
#Test
VALUES
('Tom', 30, 'Something', 1, 'ghjk'),
('Jen', 23, 'Something', 2, 'fjfor'),
('Jim', 22, 'Something', 3, 'ghop'),
('Tom', 35, NULL, 4, 'asop')
DECLARE @SearchString VARCHAR(MAX)
SET @SearchString = '%gh%'
SELECT
*
FROM
#Test T
WHERE
EXISTS
(
SELECT
*
FROM
(
SELECT T.* FOR XML PATH, TYPE
) X(C) -- Turn all columns into XML
CROSS APPLY
X.C.nodes('//*/.') E(V) -- Get all XML elements
WHERE
E.V.value('./text()[1]', 'VARCHAR(MAX)') LIKE @SearchString -- Filter on value of the XML element
)
DROP TABLE #Test
Might be very slow on large tables because each row has to be composed into XML before filtering.
P.S. Tested on SQL 2008 R2, but I suppose this works on SQL 2005 as well
November 4, 2021 at 2:44 pm
you may use this query it may help
select * from mytable
where CONCAT(field1,field2,field3,...) like '%string%'Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply