-------------------------------------
Articles
-------------------------------------
ID | Date | Headline | Body
-------------------------------------
I'm trying to write a stored procedure that will search for a given keyword. Here's what I have:
CREATE PROCEDURE sproc_SearchArticles
(
@keyword varchar(50)
)
AS
SELECT ID, Date, Headline, Body
FROM Articles
WHERE Body LIKE %@keyword%
ORDER BY Date DESC
RETURN
That keeps giving me an error. If I put quotes around it, it will literally look for the term @keyword anywhere in the body.
Hi, Im trying to create a simple search page to return a list of products from a table in a sql database. Id like to be able to search a product description column in a table I have called products using the contents of a text box. I was wondering how i can go about getting the search to check for all words the user types in rather than just a single word or an exact phrase. Im currently using the following sql query SELECT [product_title], [product_description] FROM [products] WHERE ([product_description] LIKE '%' + @product_search + '%') this works fine for single words and exact phrases but if i had product called 'fred w bloggs' and i enter 'fred bloggs' it will not return anything. Please could anyone suggest how i shoud go about this? Im not sure if my web hosting company will enable full text search or will this be required? Thanks for any help! pete_ (very new to asp.net!)
Hi all I need to implement a search function in Asp.net. Its nothing complicated, just find out whether a value exists in a particular table and return a boolean accordingly. However, the table has about 300,000 records. Is simply querying the table from the web (SELECT barcode FROM tblProducts where ID=123) the best way considering the releant column has been indexed.Any pointers would be appreciated. Thanks
Hello new in sql and asp.net here.I have this Select * from InvoiceHeader where TransactionDate > '03/16/2008' That code isn't working instead of returning a row with a transaction date that is greater than 3/16/2008 what it return is all the row. How can fix this? Thanks!
Hello all, I am trying to build a simple search page using SQL Server 2005, Visual Web Developer in C#.I have enabled Full-Text Search on my DB and created the catalogs and indexes, but I am lost on the next step to build a search form that calls the data based on the users input in a textbox. Any suggestions? Thanks!Travis
I'm not trying to do anything too fancy; given a string, I just wanna see if anything in the column of my database matches the string. I'm using an SQL query that takes a string, then selects the data using LIKE %searchword%.This works fine when the user enters only one word. But I guess you can see that a problem arises when they enter more than one word.So how can I implement a very simple search that will take more than one word?
hello I have this code Select Waste.WasteName AS [Waste Name], InvoiceDetail.Volume, Branch.BranchLocation AS [Branch Location] From InvoiceHeader INNER JOIN InvoiceDetail ON InvoiceHeader.InvoiceNo = InvoiceDetail.InvoiceNo INNER JOIN Waste ON InvoiceDetail.WasteID = Waste.WasteID INNER JOIN Branch ON InvoiceDetail.BranchID = Branch.BranchID Where WasteName = 'Sludge' AND InvoiceDate >= '" + TextBox1.Text + "' AND InvoiceDate <= '" + TextBox2.Text + "'; The problem is everytime I will search for example InvoiceDate 03/27/2008, I need to add one day for example --> 03/28/2008 for me to be able to get that Invoicedate 03/27/2008.. What's do you think is the problem with my code? Thanks!
I'm very new to SQL so please forgive my ignorance... I've made a simple .net search page which queries an sql database with the following query, (in a stored procedure): SELECT Category, Number, RegisteredUser, DeptName, Surname, Forename, Site, IDFROM tblTelephonesWHERE (@surname IS NULL OR Surname LIKE @surname) AND (@site IS NULL OR Site = @site) AND (@deptname IS NULL OR DeptName LIKE @deptname) This works fine, as expected if i leave fields null or enter an exact match, but I (of course) have to add a wildcard in my search string for a wildcard search. For example, looking for 'duncan' i need to enter 'du%' or 'duncan'. What I really want is for all searches to have wildcards behind them so only the first few characters need be inputted, and I could just search for 'd' or 'dun' without adding the '%' to get 'duncan'. I think I am aware of the implications of this approach and do want to go ahead as there are only about 850 records. Any help or links to useful articles would really be greatly appreciated.
This question probably applies to Visual Web Developer but I don't know if it belongs in this Forum or that, sorry if you have to move it. Hi there kind person reading this I've created a stored procedure in SQL2000 that selects records where (name = @name) AND (site = @site). It works as I have tested it with Enterprise Manager. I have also created a page with VWD that has a drop-down list populated with the site records and a text box. I have put an SQL data source on the page that is configured to select from the stored procedure. However, I can't set the @name and @site variables to collect from the ddl and txtbox through the wizard! I've tried selecting a Control as the source for @site but neither the ddl or txtbox appear in the control dropdown list. So I want to crack it with handwritten code. Does anyone know of a post or article that can help me link the ddl and txtbox to a search button, which calls the stored procedure with the chosen variables allowing me to put the returned data into a gridview? I've been through a great walkthrough for VWD (Student Activities) but it doesn't seem to apply to the latest version - or there's something wrong with my (seems fine though) instalation. Thanks if you can help, I owe you a
------------------------------------- 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 SELECTID, Headline, Article FROMArticles WHEREArticle LIKE '%' + REPLACE(@keyword, '%20', '%') + '%' RETURN
There'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?
I have a search form in an ASP.NET/VB page. The form has the input text box and the button "search". The keywords are passed in the URL to results.aspx.
Here is an example of what I get in the URL, when I write the keywords "asp", "book" and "london" in the input text box and click "search": results.aspx?search=asp%20book%20london
The database table has 3 fields: "id", "title" and "description".
I want to display all the records where at least one of the keywords is found in any of the 2 fields "title" and "description".
1. I suppose I need to get the words out of the URL to be used by SQL. Maybe: string[] searchString = request.queryString("search").split('search'); ??? How can i make this run when page loads? I supose i need it. Right?
2. How should the SQL look? I supose i need to use the "Like" command SELECT * FROM books WHERE title LIKE ... But how to I use it if I can have 1, 2, 3, ... keywords?
I'd like to build a simple search engine against Sql Server. Does .NET Framework provide some class to help in this task? From what I've heard there is an interface which implements it..
I am trying to write a simple search page that will search all the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:
SELECT Table.* FROM Table WHERE * = @USERINPUT But thats not working. Can someone help.? Thanks..
I've got an nvarchar(max) column that I need to transform with some simple text processing: insert some markup at the very beginning, and insert some markup just before a particular regular expression is matched (or at the end, if no match is found).
Since the SSIS expression language doesn't support anything like this, is a Script Component the only way to go? Does Visual Basic .NET provide regular expression matching?
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch ( Id int not null, AllText nvarchar(400) ) create unique index test_tfts on TestFullTextSearch(Id); create fulltext catalog ftcat_tfts;
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)' AND 'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on... so i thought create site search table, with the columns: id, text, url and to write every thing to this table. but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server... what do you think?
I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed --The Path variable is set to the bin folder for the Adobe iFilter. --SQL Developer version 64 bit on both Windows 7 and Windows 8. USE master; GO DROP DATABASE FileTableStudy; GO CREATE DATABASE FileTableStudy ON PRIMARY
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3 FROM tbl1 t1, tbl2 t2 WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
I have Sql server 2005 SP2. I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row. I have a table testtable where description has word Extinguisher.
If i run a query with *ting* it doesn't return any row. select * from testtable where contains(description,'"*xting*"') ;
But it works if i do select * from testtable where contains(description,'"Exting*"') ;
The Full text search document says it supports substring search. Is it an issue with sql server 2005?Please help.
I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:
select * from Mytable where contains(MyColumn, '123.345.')
I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:
select * from MyTable where MyCOlumn like '123.345.%';
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
hi there, i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out can anyone help? thanks
