I use SQL server to connect to a HUGE odbc compliant db with 1000's of tables. The db contains tons of data. I am looking for specific data elements. Is there any way to search the entire db for a data value?
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
Hi, i want user to type a string in textbox, and i will search, if textbox.text exists in my SQL database table or not. Below is the code i'm using :---------------
string txtname = (txtEmpName.Text).Trim(); sQuery = "select * from tbUser where UserName='_username'"; sQuery = sQuery.Replace("_username", txtname); SqlConnection objConnection = new SqlConnection(source); objDataAdapter = new SqlDataAdapter(sQuery,objConnection); // DataTable dtTable = new DataTable(); if (objConnection.State == ConnectionState.Closed) objConnection.Open(); objDataSet = new DataSet(); objDataAdapter.Fill(objDataSet); // objDataAdapter.Fill(dtTable);
or i want to search if "txtname" exists in "tbUser" table.
I using multiple keywords to search and filter my data by using Stored Proc and retrieve the results in GridViewStored Proc ========ALTER PROCEDURE dbo.search ( @eName nvarchar(30), @ID nvarchar(7), @cID nvarchar(10), @eCC nvarchar(3) ) AS SELECT eName, ID, cID, eCC FROM iTrns WHERE (ID LIKE '%' + @ID + '%') and (eName LIKE '%' + @eName + '%') and (cID LIKE '%' + @cID + '%') and (eCC = @eCC) ======== My code behind========protected void Button1_Click(object sender, EventArgs e) { GridView1.DataSource = GetTable(); GridView1.DataBind(); }public DataSet GetTable() {SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["***"].ConnectionString); SqlDataAdapter Adptr = new SqlDataAdapter();SqlCommand cmd = new SqlCommand("search", SqlCon); cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@ID", TextBox2.Text); cmd.Parameters.AddWithValue("@eName", TextBox1.Text);cmd.Parameters.AddWithValue("@cID", TextBox3.Text); cmd.Parameters.AddWithValue("@eCC", TextBox4.Text); Adptr.SelectCommand = cmd; DataSet ds = new DataSet();return ds; SqlCon.Close(); } ======== Sometimes it retrieves the whole data !! and sometimes retrieve nothing !! Any hint ?? Thank you
We purchased a software package that has a built in e-mail package. We now find ourselves in a position to audit the e-mails that are flying around. But the vendor is not telling us the name of the table that contains the text/body of the e-mails in order to audit the e-mails. My boss would like me to dump the tables to a text file and search through them looking for key words. There has to be a way to do that without dumping the tables, isn't there? Any information would be appreciated. Thank you! Jena
I have read recommendations about searching encrypted data. Typically, they involve creating a MAC (message authentication code) table. One of the elements of that table is a HASH of the encrypted data (plus a Mac key) that is used as an index for searching. Is that HASH as secure as the encrypted data itself, or is this approach less secure? If it is less secure, then may I assume that approach is the only feasible way to search data encrypted by nondeterministic algorithms?
Hi there, I need to develop a module and wondering what would be the best implementation...
I get a list of files from a text file and store it in a Recordset Destination (object variable "CUST_INV_LIST").
I need to check that all the files in a directory are in the list. I can loop through the files in the directory using a ForEach container, but how do I check if it is in the CUST_INV_LIST recordset?
I thought about using another ForEach container to loop through the recordset, check if the physical file is equal to that row, if so set a flag, ... but it's neither elegant nor effective.
Another option would be to use a Script Task to search for the physical file name in the recordset. I tried with the Data.OleDb.OleDbDataAdapter, etc. but I get and error when I declare Data.DataTable. Anyways that method of accessing a recordset is not recommanded and seems complicated.
I am saving large text document in an image type of column in a SQL Server 2000 table. How will I set up searching of words/ phrases for data stored in this column?
Hi everyone,My company has a website, use ms sql server. One table has more than 1000000 records. When users search data from this table(such as search records which contain the word "school" in NewsTile field.And the server often occurred deadlock error.How can I improve it?Thanks.P.S. The table has these fields:NewsIDNewsTitleNewsContentNewsClickTimesNewsInsertTime
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 am learing VWD as I build a site and while I can use the various controls (GridView, FormView) to set up operations and retrieve/display database information, I need to be able to codebehind to do SQL operations for retrieving data to fill paper forms and to modify the database. After I retrieve data from the database, I need to do a lot of computation befor I build a paper quote that is emailed to a customer. I have written a subroutine to which I pass my sql query statement and I can add a new entry and edit an existing entry. The problem is a simple query to read an existing database entry. If I write a simple command : Cmd = SELECT PartNumber, PartName, PartsAvailable FROM Inventory WHERE PartNumber = "P7226" , the command is executed but the return data falls on the floor somewhere as I dont know how to code to put the results in an array or in fact, have any idea on how to find the returned data. Can anyone help with this simple(?) problem? My sub that handles the sql command is shown below. I have also been looking for the way to code in VB to cause existing controls like GridViews to access, edit, and insert but I havent been able to find a paper or tutorial. If anyone has seen this information, it would be helpful also.Protected Sub CallSQL(ByVal Cmd As String) Dim conn As New SqlConnection("Data Source=.SQLExpress;AttachDbFileName=C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True") 'Data Source=.SQLEXPRESS;AttachDbFilename="C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True)Dim sql As String = String.Empty sql = Cmd 'Open the connection to the database and execute he SQL conn.Open()Dim command As New SqlCommand(sql, conn) command.ExecuteNonQuery() conn.Close() End Sub
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.%';
Does anyone know how to how to performance case-insensitive search onXML data type in SQLServer 2005? Or I have to convert all the xml datato lower case before I store it?Thanks in advance.John
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
My site uses a text box to allow visitors to search products. I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
E.g. megger meg mft megger (proper order in the database is megger mft) mft1710 (using 1710 should find the product)
This is my select statement (classic ASP)
Code: <% Dim RSResults__param5 RSResults__param5 = "xxx" If (Request("searchme") <> "") Then RSResults__param5 = Request("searchme") End If
I am developing a Recruitment Agency WebsiteI am using MS SQL Server in which I have a table called CV_Details which stores all the details of different Job Seekers. I have stored all the Word and PDF docs of the Job seekers in a 'mycatalog' using Index Server and stored the path in the CV_Details table along with rest of the cv details. Basically I want to download the cv's which i get after making a search in the doc's. I am able to get the filename, Path, Filesize by using Index Server Query.Is it anyway possible to extract all the CV Details data by matching the path.I want the cv details from the SQL Server of all the Related Paths at the same time not one at a time. Is it possible.I tried alot but i'm going nowhere. I'm giving my code below. Can anyone help me please.Thank you very much in Advance string strCatalog = "TestCatalog", strsearchstrings=""; string strQuery=""; strsearchstrings = txtsearch.Text; strQuery = "Select Filename,PATH from Scope() where FREETEXT('" +strsearchstrings+ "')"; // TextBox1.Text is the word that you type in the text box to query by using Indexing Service. string connstring = "Provider=MSIDXS.1;Integrated Security .='';Data Source="+strCatalog; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstring); conn.Open(); System.Data.OleDb.OleDbCommand objcmd = new System.Data.OleDb.OleDbCommand(strQuery, conn); System.Data.OleDb.OleDbDataReader objRdr; objRdr = objcmd.ExecuteReader(); DataGrid1.DataSource=objRdr; DataGrid1.DataBind(); objRdr.Close(); conn.Close();
Hi guys I need to create an "advanced search" which will allow the user to narrow down his results. Ideally I'd want him/her to use the same search criteria form for each iteration, with a checkbox called "Search within results" type of thing. Now what I was wondering if there was any existing literature on how to effectively do this. I have tried doing it just through SQL Statements but they are becoming very messy and large. Is it possible to do this by searching the initial dataset, returning dataset #2 and then if a 3rd "search within results" is done apply the search against dataset #2 and return dataset #3 etc? Many Thanks John
--set ANSI_NULLS ON --set QUOTED_IDENTIFIER ON --go -- -- --ALTER PROC [dbo].[spEventTagCloud] --as --BEGIN DECLARE @RECORDCOUNT INT; DECLARE @SearchString varchar(2000); DECLARE @QRY VARCHAR(2000); DECLARE @SE VARCHAR(2000); SELECT @RECORDCOUNT=COUNT(*) FROM TBEVENTS DECLARE @ST INT; SET @ST=1; CREATE TABLE #TEMP2 ( MYTAGS VARCHAR(2000) ) --CREATE TABLE #TEMP3 --( -- TAGCOUNT INT --) CREATE TABLE #TEMP1 ( STR1 VARCHAR(2000) ) WHILE @ST<@RECORDCOUNT BEGIN SET @QRY='SELECT TOP ' +CONVERT(VARCHAR,@ST)+' EVENTTAG FROM TBEVENTS' --PRINT @QRY INSERT INTO #TEMP1 EXEC (@QRY) SELECT @SEARCHSTRING=STR1 FROM #TEMP1 SET @ST=@ST+1 declare @i1 int; declare @i2 int; declare @MatchType int ; set @MatchType=0; declare @Word varchar(100); declare @Words table (Word varchar(100) not null); declare @WordCount as integer; DECLARE @TEMPWORD VARCHAR(2000); begin set nocount on if (@MatchType != 2) begin set @SearchString = ' ' + @SearchString + ','; --print 'Search String is :::: '+ @SearchString set @i1 = 1; while (@i1 != 0) begin set @i2=charindex(',', @SearchString, @i1+1) --print @i1 if (@i2 != 0) begin set @Word = rtrim(ltrim(substring(@SearchString, @i1+1, @i2-@i1)))
SET @TEMPWORD=@WORD; SET @TEMPWORD=REPLACE(@TEMPWORD,',','') INSERT INTO #TEMP2 SELECT @TEMPWORD --print 'Search WORD is :::: '+ @WORD
if @Word != '' insert into @Words select replace(@Word,',','') end set @i1 = @i2 end end else insert into @Words select ltrim(rtrim(@SearchString)) set @WordCount = (select count(*) from @Words) Declare @wordtemp varchar(2000); set @wordtemp=@word set @wordtemp=replace(@word,',','') --INSERT INTO #TEMP2 SELECT @WORDtemp END END SELECT mytags'Tag' , count(mytags)'Count' FROM #TEMP2 group by mytags ORDER BY [COUNT] DESC --SELECT * FROM #TEMP1 DROP TABLE #TEMP1 DROP TABLE #TEMP2 --DROP TABLE #TEMP3 --END
________________________________ THE ABOVE EXAMPLE FOR A TAG CLOUD MY TAGS ARE AS FOLLOW
EVENTTAG _________ ASP.NET, C#, VB.NET WELCOME TO ASP.NET ASP.NET BOOKS,C#.NET BOOKS
I WOULD LIKE TO SELECT ALL COLUMNS FROM MY TABLE SEARCH TAG IS ASP.NET THE FOLLOWING CODE WILL GENERATE AND SPLIT IT AS
TAG ____ ASP.NET c# VB.NET WELCOME TO ASP.NET ASP.NET BOOKS C#.NET BOOKS
HOW TO SEARCH AN EXACT MATCH AS ASP.NET THE ONLY ONE ROW