sql server search query

Posted on 2009-12-23
Last Modified: 2012-05-08
place search term in 'SEARCH_TERM_WE_ARE_LOOKING_FOR'

this query will find a search term in a database of type varchar or int

but it does not find
[orderid] [int] IDENTITY(1,1) NOT NULL,
declare @sql varchar(8000), @tbl varchar(255), @col varchar(255), @data varchar(50)


declare cur_tbl cursor for

      select, from sysobjects a, syscolumns b, systypes c where = and a.type = 'U' and c.xtype = b.xtype and in ( 'varchar', 

'nvarchar', 'text', 'ntext' )

open cur_tbl

fetch next from cur_tbl into @tbl, @col

while @@fetch_status = 0


      set @sql = '

                        if exists (select * from [' + @tbl + '] where convert( varchar(255), [' + @col + '] ) like ''%' + @data + '%'')

                              select tbl=''' + @tbl + ''', col=''' + @col + ''', [' + @col + '], * from [' + @tbl + '] where convert( varchar(255), [' + @col + '] ) like ''%' + @data 

+ '%''




      fetch next from cur_tbl into @tbl, @col


close cur_tbl

deallocate cur_tbl

Open in new window

Question by:rgb192
    LVL 51

    Expert Comment

    by:Bill Prew
    If the column you are trying to search actually contains the string you show:

    [orderid] [int] IDENTITY(1,1) NOT NULL,

    then the brackets will confuse the LIKE clause.  You can work around this by specifying an ESCAPE character, take a look at the following:

    LVL 10

    Accepted Solution

    it is not looking for integers at the moment.
    just add in the sixth line after 'ntext' and before )

    , 'int'

    so it should be

    'nvarchar', 'text', 'ntext', 'int' )

    and it will work
    LVL 25

    Expert Comment

    and you are sure this text is exactly written like the string you concatinate?

    Take the proof  before the  
    add a statement to let you show your sql
            print @sql

    Now you see what is executed, copy that and paste it to execute it yourself ..... correct it until it works

    Author Closing Comment

    thanks, now i can find int

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    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…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now