To retrieve records from SQL Server database

5 pts.
Tags:
Query optimization
SQL
SQL Server development
SQL Server Query
Hello All, I have a requirement with sql server. Please find below the same. In database say we have a column named "Name" in a table. The records that are available in the table are say like Column_Name = Names Lean Metric Sequence Lean Operation Metric Lean Cleaning and so on.... Now, through some application we are giving the input string as Project, so all the three records shown above should be retieved. In the application i have filtered the condition like "Select name from table where name contains "Project". so i am now getting all the three records. But i am also getting the record "Cleaning" as Lean is available as part of Cleaning. Now, I should get records like cleaning. How can I frame a query for this? The thing is that application should search for the name that are anywhere in the column(in our case it should etch records corresponding to Lean and Cleaning.. Thanks in Advance. Regards Arun

Answer Wiki

Thanks. We'll let you know when a new response is added.

This is not clear enough, at least for me.

I’m not sure whether you want to get records like ‘cleaning’ or not.

If you want to get only records containing the word ‘lean’, then you should use something similar to this:

<pre>SELECT name
FROM table
WHERE name like ‘lean %’
or name like ‘% lean’
or name like % lean %’
or name = ‘lean’</pre>

If you want to get all records containing ‘lean’ (including cleaning and others) then it is as simple as:

<pre>SELECT name
FROM table
WHERE name like ‘%lean%’</pre>

If this is not what you meant, please provide the exact query that you are currently using, and some example data of what you are getting and what you would like to get.

——————————————-

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    You will not be able to always get the right answer with a "like" query against a text field. Consider the following case: "Clean living" - this will satisfy the '%lean %' condition. The only way to find "words" is to create a second column containing the "words" separated (and preceded/followed!) by a known token, e.g., "|". You can do this with a trigger - when records are inserted into the table, generate the separate column with: <newcol> = '|' + replace(<name>, ' ', '|') + '|' Then, you can find the "word" of interest using: select .. where <wordscol> like '%|lean|%'
    3,830 pointsBadges:
    report
  • carlosdl
    The space would be the separator to find words, there is no need to use an aditional column. Look at the first part of the answer.
    69,920 pointsBadges:
    report
  • Kccrosser
    Carlosdl - you are correct, for the problem cases shown. I overlooked that you listed all the distinct cases for start/end of fields and blank separators. When I have encountered this problem in practice, there is usually a requirement to also find words that may or may not have trailing commas or hyphens (and sometimes where a comma doesn't have trailing white space, etc.). I prefer to pre-process the source data to simplify the query and avoid having to enumerate all the possible edge cases. In a generic environment with the requirement to find "words" that may be surrounded by any combination of space, hyphen, period, and/or comma, the simplest solution I have found is to replace all the "non-text" stuff with a separator character that isn't in the desired text set and then use that as a start/end symbol when searching the text. <searchcol> = '|' + replace(replace(replace(replace(<sourcestring>,',','|'),'-','|'),'.','|'),'','|') +'|' Then searching with "... where <searchcol> like '%|'+ <word> + '|%' ..." will find that word, regardless of what non-text characters may precede or follow the word in the original source string.
    3,830 pointsBadges:
    report
  • carlosdl
    You are also right Kccrosser, things can get more complicated, and your approach will do the job in such cases.
    69,920 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following