This will work on SQL Server 2005 and later.
Since the code is building an XML string, keywords overlapping the magic 4000 character limit are fetched!SELECTp.RoutineName,
'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]
FROM(
SELECTOBJECT_NAME(so.ID) AS RoutineName,
(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body
FROMSYSOBJECTS AS so
WHEREso.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X')
) AS p
WHEREp.Body LIKE '%YourKeyWordHere%'E 12°55'05.25"
N 56°04'39.16"
I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name. At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?
Greetings, I am a php developer, and running a little bit out of deadline in a project. Can someone provide me with a VERY simple way to implement search by keyword in C#? I have already implemented a search page (according to firstname, lastname etc) that works on a drop down menu (where you have the option to choose seach by keyword) . So, I need to change something in my SQL query to make this work. I already knew from my SQL experience that the simplest and probably the SLOWEST and MOST UN EFFICIENT one was using LIKE. I don't mind using it but I can't since I will end up having something close to that: SELECT * FROM users WHERE keyword_entered LIKE @keyword; (or '@keyword) which does not work. however SELECT * FROM users WHERE keyword_entered LIKE 'somename%'; does work! I guess the trick is in putting the % after the keyword. ( I would have done that in php by putting the entered keyword in a string and than add to it % and pass it to the SQL query and I dunno how to do that in .NET)any ideas?
where Description and Description2 are text datatypes.
I'm trying to return all records where myKeyword exists as a singular word in either of these two fields.
Should I create a child table where each word in each of these fields has its own row for each product and query against that or is there an efficient way of querying this result without creating the extra child table?
I’m working on a project that will allow a user to search through approx 100,000 records in a SQL table. Three of the columns are ‘text’ fields that hold paragraphs of text. The user interface has a ‘general search’ option so that they can enter a number of key words and the database will return a count of the records found containing the keywords. At the moment I split the input and then build a query based on their input. For instance if they enter ‘hello world’ the input is split into two strings ‘hello’ and ‘world’. I then build the query in a loop and get a query like so: Select Count(ID) as myCount FROM myTable WHERE (colOne like ‘%hello%’ AND colOne like ‘%world%’) OR (colTwo like ‘%hello%’ AND colTwo like ‘%world%’) OR (colThree like ‘%hello%’ AND colThree like ‘%world%’) Unfortunately this query runs EXTREMELY slowly and just seems wrong. Is there a more efficient way I should be doing these types of searching? This method works ok on 100 records, but this is the first time I have worked on such a large database. Is it also possible to search a text column and look for exact matches? For instance I have 2 records with their textfield containing: Rec 1: the news for today is blah blah. Rec 1: this is a new item If I currently search for ‘new’ (select colID from myTable where colOne like ‘%new%’) I will get both these records, but I’d really only like to pull out the second record. Any help would be great appreciated! :)
They have provided search string examples like 'Microsoft, DBA, CA' or 'CA' or 'Microsoft, developer' or 'training'
I have script ready to remove comma from the string and store words from the input string in a temporary table. But this is the easiest part.
The Confusing part now for me is to write the optimal code for retrieving the exact match from the key_search table as there is no limit on how many words can be in the string, it can be 1, 2, 3 or more.
Any suggestions on how should I handle this search?
Is it possible to write a Stored Procedure that takes a string ofsearch keywords as argument and returns the recordset? At the mo I ampassing the WHERE String as argument.I got this technique from an Extreme Ultradev tutorial by Rick Curtisit looked quite ok:http://www.princeton.edu/~rcurtis/u...tutorial12.htmlI have to admit, one of the main reason for passing the WHERE string isthat I do not know how to do the string splitting / parsing and puttingtogether in a Stored Procedure. I bet T-SQL would be just as powerfulas VBScript if I just knew it well enough.What I liked about having built them on the web script was theflexibility allowing to potentially build an advanced search withouthaving to change the stored procedure - but this is not crucial I couldalways write several stored procedures or add parameters to the SP.Here is what I have achieved in this way:User can enter one ore more keywords separated by space.Search algorithm returns results across a number of fields where ALLsearch words are contained in any of these.Search results will always be formatted a certain way and displayed ina html table no matter how the search procedure / criteria is varied.Here is the algorithm (that now works in ASP)1. split search string into separate keywords2. build where condition based on single keyword, concatenating allsearched fields (" AND f1+' '+f2+{' '+f<n>} LIKE %<keyword>%")3. concatenate all these where conditions and pass to stored procedure.4. stored procedure takes care of all other logic (e.g. Joins, whichfields are searched etc.). It uses a string variable @SQL to build thecomplete search string and then doesexecute (@SQL);to create the recordset.I bet there is a way to move 1. 2. and 3. into the SP (and I would feelbetter if it was) but I don't have the expertise to do this. If anybodywants to help me this is very welcome.I can also post my original code to clarify, just want to avoid toolong posts.CheersAxel
I have a "columnname" and i have 120 triggers defined on tables in my database. I wanted to know as is there any way to find out how many triggers out of these are defined on the "columnname" like if i do any updatesedit to this "columnname" then how many triggers out of 120 defined are fired cascadingly. Also, this "columnname" is mentioned in 43 tables in my database. Let me know if you were not able to get my question.
I want to search for a keyword in the database. The database has approximately 30 tables and the amount of data in tables is very large. Most of the tables hold 25000 rows. The search procedure for searching a keyword that i want to use is as below. When i executed the stored proc it took 20 minutes. What i want to know is full-text search a better option than this or is there any other way out.
I have a table name like "WebSearchPage" which contains near about 16 millions records and structure is likeID, PID, Pagename, Title, MetaDesc, Meta Keyword, BodyDesc..Now I have a input parameter a "Keyword" which can be single or multiple words("Cricket/international cricket").Now I have to check if that input "keyword" is exists in any column(Pagename, Title, MetaDesc, MetaKeyword, BodyDesc) from "WebSearchPage" ..then I have to select that row..
Hi Guys, I hope someone here can help me. I am writing a stored procedure that simply searches for a given value across multiple databases on the same server. So far all well and good.Now, the problem is if the user types in more than one word into the search field.I have put a partial section of code here, there is obviously more, but most of it you wouldn't need to see. SELECT @sql = N'SELECT @count = COUNT('+ @dbname +'.dbo.orders.order_id) FROM '+ @dbname +'.dbo.orders '+ N' INNER JOIN '+ @dbname +'.dbo.customer ON '+ @dbname +'.dbo.orders.cust_id = '+ @dbname +'.dbo.customer.cust_id '+ N' WHERE '+ @dbname +'.dbo.customer.forename LIKE ''%'+ @SearchStr + '%'' OR '+ @dbname +'.dbo.customer.Surname LIKE ''%'+ @SearchStr + '%'''
EXEC sp_executesql @sql, N'@count int OUTPUT', @count = @results OUTPUT Now this code works perfectly well if the user only enters one word, however i need to make sure that the Stored procedure will function if the user enters 2 words, such as John Smith. I need the procedure to search the forename for 'john' & 'Smith' and the same for the surname. It should also work if the user type 'John Michael Smith' - if you understand.I am really struggling with this one.Thanks in advance.Darren
I have been informed that all my keyword search solutions are susceptible to SQL injection attacks. Does anyone have links discussing basic ' multiple ' keyword search solutions? I would think this is a very common routine (perhaps so much so than only newbies like myself do not know it). I have read the posts about escaping ', doing replace " ' ", " '' ", using parameters and yet every multiple keyword solution I come up with is said to be injection prone. Example: visitor enters: Tom's antiquesinto a TextBox control and the C# code behind securely generates the below call to the database. SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'Tom's' + '%' AND L_Kwords LIKE '%' + 'antiques' + '%' AND L_Display = 1 RETURN I understand that concantenting string parts using an array and then passing the sewn together string to a stored procedure exposes it to injection. I hope that my single keyword routine below is secure, if it is not then I am not understanding how parameterized SP are supposed to be constructed to protect against injection.string CompanyName;CompanyName = TextBox1.Text;PROCEDURE CoNameSearch @CompanyName varchar(100)AS SELECT DISTINCT L_Name, L_Phone, L_City, L_State, L_Zip, L_Enabled, L_Display FROM tblLinksWHERE (L_Name LIKE @CompanyName + '%') AND L_Enabled = 1 AND L_Display = 1 ORDER BY L_NameRETURN
When using FREETEXTTABLE the RANK returned seems to be partially based on keyword density. Has anyone come across a method of compensating for this keyword density so that good matches found in a lot of text and a small amount of text return the same RANK?
For an example look at the site I'm working on when someone searches for "whale watching" - http://www.yougodo.com/Search.aspx?ks=whale+watching - you can see that we are showing poor quality results at the top as our first sort order is based on RANK.
If we could remove the keyword density factor from RANK then this would allow second order sorting criteria to come in to play and move the more valuable results to the top.
Any help, pointers, advice would be greatly appreciated, Gavin.
I am searching for the key word 'Platform Customer Support' using full text search. My code is as below
Set @KeywordSearch = 'Platform Customer Support'
Select AA, BB, CC, DD from SM9..TableName A Right Outer Join SM9_Experiment..TableName C On A.IncdTouchedGSF like '%' + C.SM9GroupName + '%' Where ( Contains(A.[Description], @KeyWordSearch) And A.OpenTime Between @StartDate and @EndDate And C.Classification = @GroupNameClassification )
The code is throwing:
Msg 7630, Level 15, State 3, Line 46 Syntax error near 'Customer' in the full-text search condition 'Platform Customer Support.
I am pretty new to SQL server 2005 and have the following issue: I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format. I am having trouble composing the stored procedure to do that. Any help would be apreciated. Sincerely d
Hi All,I'm familiar with the syntax that looks like this:SELECT * FROM Users WHERE Email LIKE '%aol%'Which would return all users that whose Email column contains "aol".However, if I wanted to do that sort of a search using a prepared statement, how would I do it?I can't simply doSELECT * FROM Users WHERE Email LIKE '%@MatchAgainst%'Can somebody clue me in?
I've a table that stores operationcode for each jobnumber. The jobnumber can have multiple operationcode. From the below DDL, I need to show all the jobs that have operation codes as 2001 and 2002. In the below DDL Jobnumber 80011 has both the operation codes 2001 and 2002 so this job will display on the report.
On the other hand Job 80021 only has operationcode 2001 and I do not want this job to show up on the report.
I need to show all the operationcodes for a job if it has operationcode 2001 and 2002.
USE tempdb; GO DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , OperationCodeVARCHAR(10) NOT NULL , EmployeeCode VARCHAR(10) NOT NULL
SQL 2000.I need a query that will search a field in the table that is 14characters long and begins with a number.I have a client that was allowing people to store credit card numbers,plain text, in an application. I need to rip through the table andreplace every instance of credit card numbers with "x" and the last 4digits. I got the replace bit going, but I am stuck on how to searchfor a string in a field of a specific length.Any ideas?Thanks,--Mike
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically: 1. Why it can't really filter on a measure? 2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
Anyone please suggest me that How to search specific word present multiple times from a paragraph stored in sqlserver database using asp.net 2.0 With Regards Tapan
Hi all, We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case
select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"') even though there exist records that satisfy the condition in the table, the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"
If I remove the last 1 from the search string i get lot of records, Can anybody help me out.
I have been using the following code on a search page for some time, is has worked very well. We recently changed our database to support multiple addresses for each client. So I added the INNER JOIN on the tblClientAddresses. But now when I try to search on a ID I get an ambiguous cloumn name error on the ID. Can anyone see how I could correct this?
Thanks for any suggestions,
Sub BindDataForPaging(ByVal sortExpr As String) Dim MyConnection As SqlConnection Dim MySQLAdapter As SqlDataAdapter Dim DS As DataSet Dim ConnectStr As String Dim WhereClause As String Dim SelectStatement As String
If SearchLastName.Text = "" And SearchFirstName.Text = "" And _ SearchID.Text = "" And SearchCompanyName.Text = "" And _ SearchSal1.Text = "" And SearchComment.Text = "" And SearchAddress.Text = "" Then Message.Text = "You didn't enter any search parameters. Try Again." Exit Sub End If
WhereClause = "Where " If SearchLastName.Text <> "" Then WhereClause = WhereClause & "[LastName] Like '" & _ SearchLastName.Text & "%" & "' AND " End If If SearchFirstName.Text <> "" Then WhereClause = WhereClause & "[FirstName] Like '" & _ SearchFirstName.Text & "%" & "' AND " End If If SearchID.Text <> "" Then WhereClause = WhereClause & "[ID] = " & _ SearchID.Text & " AND " End If If SearchCompanyName.Text <> "" Then WhereClause = WhereClause & "[CompanyName] Like '" & _ SearchCompanyName.Text & "%" & "' AND " End If If SearchSal1.Text <> "" Then WhereClause = WhereClause & "[Sal1] Like '" & _ SearchSal1.Text & "%" & "' AND " End If If SearchComment.Text <> "" Then WhereClause = WhereClause & "[Comments] Like '" & "%" & _ SearchComment.Text & "%" & "' AND " End If If SearchAddress.Text <> "" Then WhereClause = WhereClause & "[Address] Like '" & "%" & _ SearchAddress.Text & "%" & "' AND " End If If ClientTypeDrop.SelectedItem.Text <> "" Then WhereClause = WhereClause & "[CLientType] Like '" & "%" & _ ClientTypeDrop.SelectedItem.Text & "%" & "' AND " End If If Right(WhereClause, 4) = "AND " Then WhereClause = Left(WhereClause, Len(WhereClause) - 4) End If
SelectStatement = "Select *,A.Address FROM tblClients INNER JOIN dbo.tblClientAddresses A ON dbo.tblClients.ID = A.ID " & WhereClause & " ORDER BY " & sortExpr
Try ConnectStr = ConfigurationSettings.AppSettings("ConnectStr") MyConnection = New SqlConnection(ConnectStr) MySQLAdapter = New SqlDataAdapter(SelectStatement, MyConnection) DS = New DataSet MySQLAdapter.Fill(DS) MyDataGrid.DataSource = DS MyDataGrid.DataBind() Catch objException As SqlException Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next End Try
Hello. I have gotten my search page to work when a user enters in the entire loan number. I want to give them the option to enter in a partial loan number. I have the following code for Access database.
If Page.IsPostBack Then MySearch = "loanno Like '*" & txtSearch.Text & "*'" Else txtSearch.Text = "" End If
I need to convert this for a SQL database search. I tried to substitute the * with % and I am still getting an error. Does anyone know how to fix this problem.
Also, It would be nice to make MySearch reference a stored procedure. This would make the page much more flexible and powerful. I am open to any recommendations. Thank you.
I have code on a website that does a search to an Access database based on what the person enters as the search criteria. My SQL code uses a like % statement for all the fields in the search criteria. The problem is that the first and third search fields do no return the search criteria that is asked for.....The 2nd search field (Description) returns whatever is asked for..... I did read up and found that the data must be character for the like statement. Anyone have any other clues or where I can read up on this???????HTML SEARCH CODE: <table ALIGN="CENTER" width="408" bgcolor="#FFFFFF" cellspacing="0" cellpadding="5"> <tr> <td width="400" bgcolor="#3366cc" colspan="2"> <form method="post" name="DaForm" action="Makeit.asp" align="center"> <div align="center"><center><table border="1" width="350" cellpadding="3" bgcolor="#3366cc"> <tr> <td align="left" width="189"><select name="TypeSearch" size="1"> <option selected value="PartNumber">Part Number</option> <option value="Description">Description </option> <option value="Mfg">Manufacturer</option> </select></td> <td width="310" align="right"><input type="text" size="30" name="DaInBox"></td> </tr> <tr> <td colspan="3" align="center" width="507"><div align="left"><p><input type="submit" name="B1" value="Search!"><input type="reset" name="B2" value="Clear"> </td> </tr> </table> </center></div> </form> </td> </tr></table>Makeit ASP COde: <%@ LANGUAGE="VBSCRIPT" %> <html> <head> <title>Your Search Results!</title> <meta name="Microsoft Border" content="lb, default"> </head> <body> <% Dim MySql
Set cn = Server.CreateObject("ADODB.Connection") cn.Open("parts") MySql = "SELECT * FROM parts" If Request.Form("TypeSearch") = "Part Number" Then MySql = MySql & " WHERE PartNumber LIKE '%" &_ Request.Form("DaInBox") & "%'" End If If Request.Form("TypeSearch") = "Description" Then MySql = MySql & " WHERE Description LIKE '%" & _ Request.Form("DaInBox") & "%'" End If Dim rs Set rs = new Server.CreateObject("ADODB.Recordset") rs.Open MySql, cn, 0,1 %> <% If rs.BOF and rs.EOF Then%> <h2 align="center">We did not find a match!</h2> <%Else%>
<%If Not rs.BOF Then%> <h2>Here are the results of your search:</h2> <table BORDER="0" width="100%" cellpadding="3"> <tr> <th bgcolor="#0366cc"><font face="Arial" color="#000000">Part Number </font></th> <th bgcolor="#3366cc"><font face="Arial" color="#000000">Description </font></th> <th bgcolor="#3366cc"><font face="Arial" color="#000000">Manufacturer </font></th> <th bgcolor="#3366cc"><font face="Arial" color="#000000">Qty </font></th> <th bgcolor="#3366cc"><font face="Arial" color="#000000">Condition </font></th> </tr> <% Do While Not rs.EOF %> <tr> <td><%=rs("PartNumber")%> <%=rs("Description")%>
I have a series of stored procedures defined in a SQL7 database and want search through all their code to find a particular text value (say the contents of a raiserror message for exampl). How can I do this without having to go into each one seperately and scroll through the code? Is it possible to do it from within SQL itself?
I'm writing a search for our site and I'm running into a few problems.When I run the query like this, it runs just fine:Declare @Keywords varchar(2000)Select @Keywords = 'modern trial advocacy canada'Select product_id, name, count(name) hitsFROM estore_productsINNER JOIN sequenceON estore_products.name like '%' +Substring(' ' + @keywords + ' ',seq,CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)+ '%'WHEREseq <= len(' ' + @keywords + ' ') andSubstring(' ' + @keywords + ' ', seq - 1, 1) = ' ' andCharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq 0Group by estore_products.product_id, nameORDER BY Hits DESCBut when I add another column (for example a column called description)from the select statement I get this error:The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.Long story short, what do I need to do to select more columns for thefinal output?Thanks,Ryan
I'm trying to create a form that allows someone to find an address from a postal code search in the database. My query works exactly as I’d like in query builder using the following select:
Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@POSTCODE+’%’
I then pass the user entered post code to the select statement which is executed. However, I’m getting some odd behaviour. Assuming there is an address in the database with the post code “LS11 0ES”...
If I search for LS11, nothing is returned;
If I search for LS11%, nothing is returned;
If I search for %LS11%, nothing is returned;
If I search for %LS11%%, the data is returned.
If I search for %LS%1%%, the data is returned (as is LS21 0ES etc. etc.).
However, I want the user to be able to enter shorter search strings and it to pull all the data back out, so they can enter a substring such as LS and it will pull out all the data without the users needing to enter the full pattern of % symbols.
If I go into query builder (in visual web developer) and enter just “LS” it works as I’d want, but not when pulled from a web page.
I'm trying to execute a full-text query from a vb.net web application.The problem I have is that in SQL Server, the syntax for a full-textsearch isSELECT *FROM tableWHERE CONTAINS( *, ' "searchstring" ')For whatever reason, VB won't run that search string unless I eliminatethe double sets of quotes:SELECT *FROM tableWHERE CONTAINS( *, "searchstring")This will not work for a search phrase - only single words, and it alsowill not work for a wild card:SELECT *FROM tableWHERE CONTAINS ( *, "search*")The above does not work - I must assume that the double quotes areneeded for both wild card searches and exact phrase searches -The problem of course is that in vb code the SQL string is alreadyenclosed in search quotes - the single quotes are then used to indicatedouble quotes within the search.I have tried using two single quotes on either side of the string, butit doesn't seem to work. ie:"SELECT * FROM table WHERE CONTAINS ( ' '" & textbox1.text & "' ')"Does anybody have any ideas?Trevor Fairchild*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
