Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Select Where Any Column Equals (or Like) Value Expand / Collapse
Author
Message
Posted Friday, July 10, 2009 4:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 31, 2009 8:18 AM
Points: 17, Visits: 14
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:

Name Age Description Field1 Field2
Tom 30 Something 1 ghjk
Jen 23 Something 2 fjfor
Jim 22 Something 3 ghop


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

Post #750935
Posted Friday, July 10, 2009 5:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:42 AM
Points: 1,949, Visits: 8,314
Other than building it in dynamic sql then no...



Clear Sky SQL
My Blog
Kent user group
Post #750966
Posted Friday, July 10, 2009 5:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 42,825, Visits: 35,955
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #750968
Posted Friday, July 10, 2009 5:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 6,594, Visits: 8,882
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #750974
Posted Friday, July 10, 2009 5:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 31, 2009 8:18 AM
Points: 17, Visits: 14
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
Post #750978
Posted Friday, July 10, 2009 6:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #750996
Posted Friday, July 10, 2009 7:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:38 AM
Points: 237, Visits: 132
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.
Post #751018
Posted Tuesday, January 5, 2010 4:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:41 AM
Points: 77, Visits: 218
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' + '%'
Post #841955
Posted Tuesday, January 5, 2010 4:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 7,161, Visits: 13,228
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' + '%'





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #841965
Posted Tuesday, January 5, 2010 7:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:41 AM
Points: 77, Visits: 218
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.
Post #842100
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse