delimitered list through sql server

• • Reply

  12 years ago

  by froten

  froten

  , Libya

  Joined 12 years ago
  Hello
  
  I have a table in SQL server which looks like this:
  
  service_id
  service_name
  service_keywords
  
  The data in service_keywords is delimitered with a colon eg. website,stuff,testing,words
  
  Now I have a webform which allows people to search (via a textbox). When they enter a word it should check all the words in the service_keywords columns and return the id's that have a match (same method as LIKE in sql statements, eg site should also match the keyword website).
  
  Can I do this in .NET or do I have to do this via a stored procedure or is there any other option available? Can't seem to figure this out.
  
  Thanks in advance.
  Report abuse
• • Reply

  12 years ago

  by royal ludhiana

  Royal Ludhiana

  Toronto, Canada

  Joined 14 years ago

  hi Kevin,

  Try to split your string stored in service_keywords.

  - Fetch the string.

  - create an string array.... like:  string[] arrkeywords;

  - then split the string fetched earlier.....like : arrkeywords = servicekeywords.Split(new string[]{","}, StringSplitOptions.None);

  Then you can compare each array element with the input word.

  Regards,

  Royal

  Report abuse
• • Reply

  12 years ago

  by Slicksim

  Simon Cooper

  Lancaster, United Kingdom

  Joined 15 years ago

  Hi,

  This is a simple database query, I dont know SQL server syntax, so this is oracle syntax that you can edit:

  [CODE]

  using oraConn as oracleconnection = new oracleconnection(configurationmanager.connectionstrings("db").connectionstring)

  using oraCmd as oraclecommand = new oraclecommand("select * from table where service_keywords like (:QUERY)", oraconn)

  oraCmd.connection.open

  oraCmd.parameters.addwithvalue("QUERY", "%" & txtQuery.text & "%")

  dim oraDread as oracledatareader = nothing

  oraDread = oraCmd.Executereader

  if oraDread isnot nothing andalso oradread.hasrows

  do while oraDread.read

  ' your code to do the stuff with the results goes here

  loop

  oraDread.close

  end if

  oracmd.connection.close

  end using

  end using

  [/CODE]

  That should see you right.  If you want the results in a dataset, use a dataadapter and call fill on it passing in the dataset.

  Regards

  Simon C

  Report abuse
• • Reply

  12 years ago

  by ahmed.alrefaie

  Ahmed Alrefaie

  Tripoli, Libya

  Joined 12 years ago
  HI You can simply much Just follow the following DIM CON AS NEW SQLconnection("cOnNeCtIoN sTING ") DIM DA AS NEW DATASET DIM DA AS NEW SQLDATAADAPTER("SELECT [ServiceID] from Tablename Where servicekeywords like '%' + @k + '%' ",CON) DA.SelectCommand.Parameters.AddWithValue("@k",TEXTBOX1.TEXT) DA.FILL(DS) Thanks my Regards .. Eng : Ahmed Alrefaie
  Report abuse