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.
| Author |
Topic |
|
Apples
Posting Yak Master
146 Posts |
 Posted - 2008-04-15 : 15:01:49
|
| I have a table of news articles:-------------------------------------Articles-------------------------------------ID | Headline | Article-------------------------------------I'm trying to write a search function that will search through the text in the Article column. Here's my stored procedure:ALTER PROCEDURE sproc_SearchNews ( @keyword varchar(50) )AS SELECT ID, Headline, Article FROM Articles WHERE Article LIKE '%' + REPLACE(@keyword, '%20', '%') + '%'RETURNThere's a few problems with this however...1. If I enter "Dog" into the search, it will skip over the article if it contains "dog". I need to convert the search term and the article text to lowercase.2. If I enter "iron" into the search, it will find articles containing "iron", but it will also find articles containing "environment". How can I fix that without affecting my multiple word search in the WHERE clause? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-15 : 15:27:24
|
WHERE Article LIKE '%' + REPLACE(@keyword, '%20', '[%]20') + '%'
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-04-15 : 23:11:41
|
quote:
Originally posted by ApplesThere's a few problems with this however...1. If I enter "Dog" into the search, it will skip over the article if it contains "dog". I need to convert the search term and the article text to lowercase.
I believe SQL is by default non case sensitive?Prakash.PThe secret to creativity is knowing how to hide your sources! |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-04-16 : 02:58:31
|
| 1. Case sensitivity depends on the collation settings you're using. To compare with the same case use WHERE LOWER(Article) LIKE '%' + REPLACE(LOWER(@keyword), '%20', '[%]20') + '%'2. Look up on full-text indexing and searching in BOL.--Lumbago |
|
|
|
|
|
|
|
|
Problems with a simple search