Select Where Any Column Equals (or Like) Value

  • 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

  • Other than building it in dynamic sql then no...



    Clear Sky SQL
    My Blog[/url]

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • 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' + '%'

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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.

  • Great idea, but you either need to handle nulls ... isnull({{column}},'') or cast them as strings... Found this out the hard way.

  • 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

  • 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

  • you may use this query it may help

     select * from mytable
    where CONCAT(field1,field2,field3,...) like '%string%'

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply