Post reply

Google search

  • May 22, 2009 at 1:16 am

    #97694

    Hi Guys

    I have a table User

    Data in UserTable

    UserId Firstname LastName Officename

    1Ningaraj NayakExcel soft

    2mandir KumarExcel soft

    3Deepak KumarExcel soft

    4Vijay SinghInfosys

    5Madhu Sudhansamsung

    6Venki prathapInfosys

    7Excel soft GDMandir

    8 Shashi kiran MCC soft

    i wanna search the users who satisfies the below condition

    If my serach text is like 'Mandir+Excel Soft'

    then i want the UserId 2 and 7

    If my serach text is like 'Mandir+Excel Soft + kumar'

    i should get UserId 2

    like google search

    please do the needfull

    Thanx

    Regards

    Ningaraju N E

  • May 22, 2009 at 2:45 am

    #998725

    Sounds like you are looking for Full-Text indexes?

    see if this helps: http://msdn.microsoft.com/en-us/library/ms142571.aspx

  • May 22, 2009 at 11:20 pm

    #999275

    Thanx Friend

    but i have 200 to 300 tables in my db... i have to apply search for all tables like i descriged above .... i think its not good practise to apply fulltext indexes on all tables..

    So could u plz help me out in coding ?

  • May 23, 2009 at 12:07 am

    #999278

    ningaraju.n (5/22/2009)

    If my serach text is like 'Mandir+Excel Soft'

    then i want the UserId 2 and 7

    If my serach text is like 'Mandir+Excel Soft + kumar'

    i should get UserId 2

    like google search

    Thanx

    Regards

    Ningaraju N E

    Hi,

    what logic your using in the free text search

    like Firstname+Officename+LastName('Mandir+Excel Soft + kumar')

    or its the open serch to may match all the column

    and Try this

    -- create table #temp

    -- (

    -- UserId int,

    -- Firstname varchar(20),

    -- LastName varchar(20),

    -- Officename varchar(20)

    -- )

    -- insert into #temp

    -- select 1,'Ningaraj','Nayak','Excel soft'

    -- union all

    -- select 2,'mandir','Kumar','Excel soft'

    -- union all

    -- select 3,'Deepak','Kumar','Excel soft'

    -- union all

    -- select 4,'Vijay','Singh','Infosys'

    -- union all

    -- select 5,'Madhu','Sudhan','samsung'

    -- union all

    -- select 6,'Venki','prathap','Infosys'

    declare @strin varchar(MAX),

    @RESULT varchar(MAX)

    select @strin = 'Mandir+Excel Soft+kumar'

    select @RESULT = 'select ''' + replace (@strin,'+',''' union select ''')+''''

    --create table #RESULT(COL1 varchar(MAX))

    delete from #RESULT

    insert into #RESULT

    exec (@RESULT)

    select * from #temp

    where Firstname in (select col1 from #RESULT )

    or LastName in (select col1 from #RESULT )

    or Officename in (select col1 from #RESULT )

    ARUN SAS

  • May 23, 2009 at 12:19 pm

    #999352

    ningaraju.n (5/22/2009)

    Thanx Friend

    but i have 200 to 300 tables in my db... i have to apply search for all tables like i descriged above .... i think its not good practise to apply fulltext indexes on all tables..

    So could u plz help me out in coding ?

    Neither is it a good practice to have 200 to 300 tables to search for data unless they are coalesced by partitioning. 😉

    We need more information. For example, do all of the tables have the same columns to be searched?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 23, 2009 at 12:30 pm

    #999356

    I'd assume that the number of words you look for are variable (?), therefore it would become really complicated (and to some extend unsafe if you create sql expressions) to create all the combinations using standard SQL syntax, while FT can really help you on this matter.

    My guess is that you won't really need to create FT indexes on 200+ tables.. If that's the case I would think of design issues (no offense here).

    FT allow you to generate a single index on multiple fields of the same table (this, imho, would obviate the issue of querying different fields) and you would also obtain the benefit of rankings while avoiding performance issues.

    I might be wrong of course, jm2c 🙂

    Cheers,

    Matt

Viewing 6 posts - 1 through 5 (of 5 total)

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