SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Best SQL- searching string appears in many column?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TomHand
Starting Member

Australia
8 Posts

Posted - 04/15/2013 :  09:19:26  Show Profile  Reply with Quote
Ok, Say, I got a table that have 3 columns: c1, c2, c3

C1 - C2 - C3
A2 - B2 - N2
K1 - B2 - N1
K1 - B3 - N1
L1 - A2 - C1

Ok, when users search for any combination of A1, A2, A3, B1, B2, ... then the system can be able to pick the rows with the closet match (it means as long as the word appears in 1 column, the system will pick it, the more words appear in more columns the closer it matches) & order them according to the closest match

Ex1: a user searches for "K1 A2 C1 N1", the system will show:
K1 - B2 - N1
K1 - B3 - N1
A2 - B2 - N2
L1 - A2 - C1

Ex2: a user searches for "K1 A2 C1 N2 B2", the system will show:
A2 - B2 - N2
L1 - A2 - C1
K1 - B2 - N1
K1 - B3 - N1

My solution is to split the search string into separate words & then search each of these words against the columns in the table. But I am not sure it is the optimum query since the DB have to search in many loops.

So if u r expert in DB, then what is the best query in this scenario?

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/15/2013 :  10:04:03  Show Profile  Reply with Quote
Well first of all answer to the last question - I am not an Expert :). But my solution would be as following (more to come from experts)

1) Turn the input into tabular format with "a comma before and after" each word.
2) Have a computed (persisted) field in your table having computation as ','+C1+','+C2+','+C3+','
3) create an index on the computed field

And then simply join InputTabularFormatData and DesiredTable.

And yes, one thing if any of the column c1, c2 or c3 is/are nullable(s) then nulls should be handled in computation so that ",ColumnValue," pattern is preserved

Cheers
MIK

Edited by - MIK_2008 on 04/15/2013 10:45:45
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

359 Posts

Posted - 04/15/2013 :  11:04:05  Show Profile  Reply with Quote
This might do it:

select c1
      ,c2
      ,c3
      ,case when c1 in ('K1','A2','C1','N1') then 1 else 0 end
      +case when c2 in ('K1','A2','C1','N1') then 1 else 0 end
      +case when c3 in ('K1','A2','C1','N1') then 1 else 0 end as sortwheight
  from yourtable
 where c1 in ('K1','A2','C1','N1')
    or c2 in ('K1','A2','C1','N1')
    or c3 in ('K1','A2','C1','N1')
 order by sortwheight desc
         ,c1
         ,c2
         ,c3
Go to Top of Page

TomHand
Starting Member

Australia
8 Posts

Posted - 04/15/2013 :  21:58:26  Show Profile  Reply with Quote
Hi bitsmed,

Thax u very much for your help. I tried ur query it worked perfectly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000