dynamic query

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  February 7, 2007 at 2:28 pm

  #116114

  I have a table(emp_names) with two columns e_id and name and the tables data is shown below.

  e_id  name

  1 sam

  2 ram 

  3 john

  4 pollock

  5 chappel

  6 allen

  7 koby

  8 griffith

  9 sally

  10 neil

  I have to search a text field in another table with the words in the name field.

  Example.

  SELECT dept_id,dept_name

   FROM dept_heads WHERE name like '%sam%' OR name like '%ram%' OR name like '%john%' OR name like '%pollock%'

   OR name like '%chappel%' OR name like '%allen%' OR name like '%koby%' OR name like '%griffith%' OR name like '%sally%' OR   name like '%neil%'

  The rwos in the table may be added. So when a new row is added to the table tha query should also reflect that change.

  e_id  name

  1 sam

  2 ram 

  3 john

  4 pollock

  5 chappel

  6 allen

  7 koby

  8 griffith

  9 sally

  10 neil

  11 keith

  SELECT dept_id,dept_name

   FROM dept_heads WHERE name like '%sam%' OR name like '%ram%' OR name like '%john%' OR name like '%pollock%'

   OR name like '%chappel%' OR name like '%allen%' OR name like '%koby%' OR name like '%griffith%' OR name like '%sally%' OR   name like '%neil%' OR name like '%keith%'

  How can develop this query dynamically.

  Thanks.

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  February 7, 2007 at 2:44 pm

  #688401

  select d.dept_id, d.dept_name, d.[name] from dept_heads d inner join emp_names e on d.[name] LIKE '%' + e.[name] +'%'

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  February 7, 2007 at 3:10 pm

  #688412

  Hi Jason,

   

  It worked really good.

   

  Thanks.

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  February 7, 2007 at 4:33 pm

  #688426

  Why are you using %% (%sam%, etc)?

  If you are searching a text field %sam% will return:

  balsam

  sam

  same

  sample

  samuel

  Instead, do % sam %.

  -SQLBill

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  February 8, 2007 at 7:03 am

  #688547

  Hi, thanks for both of you on ur help. Infact I have built my query in the way sqlbill has suggested.

  But I am into another issue. Some times my text column has html tags also. So for example if I am searching for a word border my search query will pull the the border from the html tags.

  <h1><img border="0".......

  So how can I by pass all the html tags when I search for a string.

  Thanks.