Best SQL- searching string appears in many column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Best SQL- searching string appears in many column?

Author

Topic

TomHand
Starting Member

8 Posts

Posted - 2013-04-15 : 09:19:26

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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-15 : 10:04:03

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-04-15 : 11:04:05

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

TomHand
Starting Member

8 Posts

Posted - 2013-04-15 : 21:58:26

Hi bitsmed,

Thax u very much for your help. I tried ur query it worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources