Hi, I am using the following sql for search function:
Select * From TableABC Where condition LIKE '%xyz%'
However, it does not provide good results, some results is not accurate, I would like to ask, is it any better mechanism for searching?
Please advise. Thanks~
James
I am trying to write a used car search function I want the user to be able to select a MAKE from the 1st drop down list and a MODEL from the second drop down list and click a button and for the data to be shown in a gridview, the code below is what I have so far and works i.e when user selects a make it shows all cars for that make, but now how do I take it further and add the model on there so that when the user selects i.e BMW as make and Z3 for model it only shows all BMW Z3'S
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>">
</asp:SqlDataSource>
<br />
<asp:DropDownList ID="ddmake" runat="server" DataSourceID="SqlDataSource2" DataTextField="make"
DataValueField="make" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT [make] FROM [used]"></asp:SqlDataSource>
</div>
<asp:button id="button1" runat="server" text="search"/>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>
</form>
</body>
</html>
Here is the code behind the page
Protected Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
SqlDataSource1.SelectCommand = "select * from [used] where make like '%" & ddmake.Text & "%'"
SqlDataSource1.DataBind()
End Sub
We are in the process of developing an onlinestore like www.componentsource.com.We need to search for a product within our website.We already created pages using html.I have to take the 1st para of the product description and display. I don't want to save the description in the table how to do it?
View 1 Replies View RelatedHi all I have a search function in one of my database's that when you click on the command button it window comes up that ask you for the last name. What I would like to do is alter it so that users can type in SM and get all the names that start with SM or Ma?? Like a Starts with. How can I do that
= N'[@Enter_LastName].[dbo.Revocatiions] ALike [ta%]'
CREATE FUNCTION dbo.Revocations
(@Enter_LastName nvarchar(50))
RETURNS TABLE
AS
RETURN ( SELECT [SSN], [TM#], FirstName, LastName, ReasonofRevocation, Notes, [I/R #], Date
FROM dbo.Revocations_Tbl
WHERE (LastName = @Enter_LastName))
Hi All,
Here's my problem:
I create titles and submit them for approval. Once approved I can edit them again and either save them or submit them for approval again.
My problem is:
When I am creating a title and saving it in the drafts and searching for the title, I am able to find it in the result page. This is before approval.
When I submit the same title for approval and it's approved and then I change the title and save it as draft and then search the new title I cant find it. But when I search with the old title it shows me the result but with the new title name.
When I check the db and search for the new title no records found.
When I check the db and search for the old title the system returns me the record with the same old name.
So when I search the old title in the db it returns the old title.
So when I search the application for the new title it returns the old title.
Help Appreciated!
Using SQL Server 2005
I have a table search the record. The user can search by name, id, status, address and other information.I want to make the search function more robust. For example, when the database has a record with name = "Michael Jackson", either typing any subset of the name will show this record.I would like to know if there is any easy way to implement this with VS2005 and SQL2005 Express.Thank you
View 3 Replies View RelatedHelloIn my website I have a textbox to search some products, but it would like to extend my search-function so that it could find special characters. For example:When I search Mexico, I would like to find México. Is there an easy way to do this?Thanks in AdvanceWesley
View 1 Replies View RelatedHi all,I'm very new to ASP.NET stuffs, I'm trying to write a Search function for my website... I have two text boxes, one if called "SongTitle" and the other is "Artist"... Now I need to populate the GridView to display the result, based on the input of the textbox... So if only the "SongTitle" have input, it will search for the Song Titles on the database... if the Artist is searched, then it will return the artist... If both text boxes have value in them, then it need to check for both fields in the database and return the correct item... For the "Artist", I have 2 columns in the Database (originalArtist and performer), so for the Artist select statement, it need to check both columns on the table, if any of them match then it will return the item. Any help would be greatly appreciated,Thank you all,Kenny.
View 15 Replies View RelatedHi,
How to search the last occurance of one String in to another string..Using function PATINDEX() in a column of data type Text..
plz Give some solution i really need it ...
Thanks Yogesh
The 'LIKE' function looks for words that start with whatever is in the like condition. Is there an sql function similar but will look and compare at any part of the search string.
For example I am using a webservice in dot net to populate a dropdown list using this sql
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText
In this table there is a compound called SILCAP310 and I would like the search function to pick up 310 if I put this into the @prefix parameter. (but I would still like the search to perform like the 'LIKE' does also.
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText or compound_name SearchPartString @prefixText
Thanks in advance
Hi All
Is there a function to search for characters in the string simlilar to the IndexOf function is C#.net?
I need answers urgently.
Anyone have the code that would allow me to see if any of my sprocs contain references to a function? I imagine it would someting like select name from sysobjecst where charindex(whatevertextis, 'ufnName') > 0
Thanks
Hi all,
I have a search function in my website, which is fully functionable.
But currently, this is the issue that I am having problem with.
For example:
I have only the name 'John' in my database.
When the user search for 'John Tan', no results is shown as there was no such name in the database
But if this happens, I want 'John' to be always returned and shown, no matter what 'John' the user search - E.g "john tan", John Loh", john wee"
So, how do I go about doing this? I have done some research on how to go about doing it, what I found useful was the .Replace() function. However, I can never predict in advance what user is going to key in.
Any help is welcome as this is a rather pressing problem of mine. Thanks for all the help in advance!!!
Hi All!!!
I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem:
The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned.
So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character.
Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID
2 FROM CRPL
3 WHERE (Trans LIKE '%' + @Trans + '%') OR
4 (Name LIKE '%' + @Name + '%') OR
5 (Address1 LIKE '%' + @Address1 + '%') OR
6 (Address1T LIKE '%' + @Address1T + '%') OR
7 (Address2 LIKE '%' + @Address2 + '%') OR
8 (Address2T LIKE '%' + @Address2T + '%') OR
9 (City LIKE '%' + @City + '%') OR
10 (CityT LIKE '%' + @CityT + '%')
Thanks for all your help in advance!!!
Hi Everyone-
i woder if someone know Built in function to search the occurence of char in string in T-SQL
and iam not talking about the Index of the first occurence (e.g CHARINDEX )
but it is more generic
i want to send to the function to ask about the index the second or the third or N occurence of the char.
and i wonder if it is built in for performance reasons
example
getseachIndex("M|d|d","|",2)==> the result is 4
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
View 2 Replies View RelatedI'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
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
View 3 Replies View RelatedOur 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;
[Code] ....
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
[code]....
How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?
Should I something download?
Please help me to create an SQL Server 2000 Stored Procedure for using prefix and suffix terms.
Example:
Say I want to find "Terminator" (1984).
I want to be able to use "Term" or "ator" as search results and still return the proper record.
Here is my Stored Procedure creation sql:
CREATE PROCEDURE sps_searchTitles(@searchTerm varchar(255)) AS
SELECT * FROM Video
WHERE FREETEXT (Video.*, '"*@searchTerm*"')
GO
--- The above does not appear to properly check both prefix ("Term---") and suffix ("---ator") terms.
I am trying to accomplish what is similarly done with LIKE '%term%'.
thanks, YM
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:
123.345
123.345
123.345.444
123.345.555
123.345.666
123.345.444.777
123.345.444.888
123.345.555.999
I am trying this query:
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
View 5 Replies View Relatedhi 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
for example:
SELECT * from [table1] WHERE CONTAINS([msgcomment], '"fast" NEAR "performance"')
would always slower than
SELECT * from [table1] WHERE [msgcomment] = '%fast%performance%'
Why? and how can it be solved? can you help me?
:)
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
[Code] ....
I'm experiencing an anomaly with my Full text index.
Setup : SQL Server 2005
Indexed Table:
Assets
ID - int (PK, auto increment)
Ref - varchar(50)
[code]....
I have re-built the full-text index, no change.
Are there any big differences between the two search techniques? It seems like they are both very similar.
SELECT * FROM TABLE1 WHERE TEXTFIELD1 LIKE '%DATABASES%'
SELECT * FROM TABLE1 WHERE CONTAINS (TEXTFIELD1 ,' "DATABASES" ')