To retrieve records from SQL Server database
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
Software/Hardware used:
Software/Hardware used:
ASKED:
February 5, 2009 3:35 AM
UPDATED:
February 10, 2009 3:06 PM
Answer Wiki:
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.
-------------------------------------------
To see all answers submitted to the Answer Wiki: View Answer History.
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|%’
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.
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.
You are also right Kccrosser, things can get more complicated, and your approach will do the job in such cases.