Searching Text In A Db Field
Sep 10, 2006
Im building up a query in my code behind. When I execute this query it returns a list of users who match the criteria.
BUT, I want to add something extra. In my DB in the tblUserData there's a field "interests" datatype nvarchar(30) which contains the numbers (comma-delimited) of the interestsID in my tblInterests. For example, my interestsfield may contain something like: 1,4,8
Now if someone wants to find someone who has number 4 and 8 as interests, how can I search in this textfield?!?
I wrote this in a SP, but I'd really like it to be possible from code-behind and build the query myself...
declare @s varchar(20)
set @s='4,8'
EXEC('SELECT * FROM tblUserData WHERE Interests in ('+@s+')')
View 1 Replies
ADVERTISEMENT
Jul 26, 2007
Hi
We have a application running on Sql server 2005, which require to browse/search text field. Does anyone know if Sql server's search/browse performance on text field is better than oracle?
The table the application will search on is a customer table that has a 10000 records in it, does this size of table casue a performance problem for sql server 2005 if I index the text field?
Please advise, thanks for your help!
Li
View 4 Replies
View Related
Mar 31, 2004
I am using the following plumbing code to search a database column for a keyword. I can't use full-test indexing so I came up w/ this work around. But It has many flaws so I'm looking for a better way. Thx in advance.
'Open sql connection
SqlConnection1.Open()
Dim datareader2 As SqlClient.SqlDataReader
datareader2 = cmdFindRowsWithKeyword.ExecuteReader
Dim strMsg As String
Dim intRowToFlag As Integer
Dim strRowsToGet As String
Dim strKeywordAsTyped As String
Dim strKeywordAllCaps As String
Dim strKeywordAllLower As String
Dim strKeywordFirstLetterCap As String
Dim FirstLetter As String
While datareader2.Read
intRowToFlag = datareader2(0).ToString
strMsg = datareader2(1).ToString
'Assign keyword as typed to variable
strKeywordAsTyped = txtSearchFor.Text
'Assign keyword as typed to variable then convert it to all uppercase
strKeywordAllCaps = txtSearchFor.Text
strKeywordAllCaps = strKeywordAllCaps.ToUpper
'Assign keyword as typed to variable then convert it to all lowercase
strKeywordAllLower = txtSearchFor.Text
strKeywordAllLower = strKeywordAllLower.ToLower
'Assign keyword as typed to variable then convert it so just the first letter is in uppercase
strKeywordFirstLetterCap = txtSearchFor.Text
FirstLetter = strKeywordFirstLetterCap.Chars(0)
FirstLetter = FirstLetter.ToUpper
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Remove(0, 1)
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Insert(0, FirstLetter)
'If the string contains the keyword as typed in all caps all lowercase or w/ the 1st letter in caps then flag that row.
If strMsg.IndexOf(strKeywordAsTyped) <> -1 Or strMsg.IndexOf(strKeywordAllCaps) <> -1 Or strMsg.IndexOf(strKeywordAllLower) <> -1 Or strMsg.IndexOf(strKeywordFirstLetterCap) <> -1 Then
cmdFlagRowsWithKeyword.Parameters("@recid").Value = intRowToFlag
SqlConnection2.Open()
Dim datareader3 As SqlClient.SqlDataReader
datareader3 = cmdFlagRowsWithKeyword.ExecuteReader
datareader3.Close()
SqlConnection2.Close()
End If
End While
datareader2.Close()
View 2 Replies
View Related
Jul 26, 2007
Hi all,
I have a very big database with me. This database has large number of tables and each table has n number of fields.
My trouble now is that I desire to search for a field name called 'empname'. Is there any query possible for me to search all tables and return me the table names which contain fields which match with 'empname'. Adding essence to the porridge I would like to know if I specify something like '%pnam%' it should match any field name which contains 'pnam' as a portion of its name. Is all these possible ??
Thanx for any help in advance,
Regards,
tvks
View 6 Replies
View Related
Jun 29, 2005
Hi again!
I have a products table with product attributes in a second table,
together they describe a full product. I have a product title, a
list of providers, description text, and keywords. I would like
to do a search across these fields, and so far my research has shown
that the Full Text Search component of SQL Server is the way to
go. However, I am not sure this will be possible based on what is
installed on the hosted server, so I am wondering if there is a unique,
cool way of doing this without Full Text Search?
Thanks,
jr.
View 5 Replies
View Related
Dec 28, 1998
Hi:
I need to search all user written SPs which have particular text in them. One way to do it is to open each SP in some notepad or word processor and search for the particular text.Is there any efficient way to do it ??
Rnathan
View 2 Replies
View Related
Mar 10, 2005
I'm using the .NET Framework 1.1 together with SQL Server 2005 Beta.
I have a search page where you can search a db using a number of fields or combination thereof. Three are text fields (patient id, surname, forename) and I can do any search using one criteria or any combination without any problem.
However, I'd also like to search on a dob (datetime field in SQL Server) and I'm having real problems in making this work. The dates are stored in the following way in SQL Server: '1938-05-31 00:00:00.000', whilst they are displayed as 'dd/mm/yyyy' on a web page, which I guess corresponds to my regional settings.
My code is shown below. I 've tried any number of combinations for the dob string with no success. I do not get any error, just no records are returned.
Does anyone have any idea what I'm doing wrong and how to correct it before this drives me crazy?!
TIA for any help.
P.S. chxPatID, chxSurname etc are all checkboxes.
<code starts>
If chxPatID.Checked Then
strSQL = strSQL & " patid= '" & txtPatID.Text & "' "
strAND = " AND "
End If
If chxSurname.Checked Then
strSQL = strSQL & strAND & " surname ='" & txtSurname.Text & "' "
strAND = " AND "
End If
If chxForename.Checked Then
strSQL = strSQL & strAND & " forename='" & txtForename.Text & "' "
strAND = " AND "
End If
If chxDoB.Checked Then
strSQL = strSQL & strAND & " dob= " & CDate(Year(txtDoB.Text) & "-" & Month(txtDoB.Text) & "-" & Day(txtDoB.Text)) & ""
End If
</code ends>
View 1 Replies
View Related
Jul 12, 2001
I have a varchar column which containd comma delimited values like
Rec# Fruits
1 Apple, Peach, Strawberry
2 Orange, Mango
3 Banana, Grape
...........
Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.
Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??
I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?
Thanks
Sumit.
View 4 Replies
View Related
Dec 2, 2003
I need to find a better way to find all parts from a particular category. Each part can appear in multiple categories.
Currently, I use the id of a catagory and search the txtCatagory field in the position of the id.
I have included the query string for you to see.
SELECT * FROM tblParts
WHERE SUBSTRING(txtCategory,@CatId, 1) = '1'
ORDER BY PartNum ASC
txtCategory is Data Type Text
This currently does work, however is causing some performance problems with my ISP.
Any help would be greatly appreciated.
View 1 Replies
View Related
Jan 4, 2008
how can install full text searching in SQL SERVER 2005
By mohsen
View 1 Replies
View Related
Jan 5, 2008
how can TRUE full text indexed property in column property in SQL server 2005?
by mohsen
View 1 Replies
View Related
Jan 6, 2008
how can enabled full-text searching in sql server 2005 on windows XP?
Thanks, mohsen
View 2 Replies
View Related
Jan 10, 2008
do support full-text searching for sql 2005 express edition? thanks,mohsen
View 1 Replies
View Related
Jan 16, 2008
how can enabled full-text searching for table columns for sql server 2005 enterprise edition?
View 1 Replies
View Related
Mar 9, 2004
I am trying to run a full text serach on one field, a Varchar 2000.
say the field contains:
(before you break the seal of your new product box, please be careful to read all the instructions) ...for example
I search for keywords that may be in this field
Like:
product box
seal
instructions
and this row is included in the result set
but I would like to leave out words like all pronouns and 'a' and 'I' ...words that aren't going to matter to the search.
Does someone know where I can stgart in doing this full text searching?
Thanks,
Eric
View 3 Replies
View Related
Oct 16, 1998
I`ve just starting playing around with version 7.0 and was wondering if anyone knows how to implement the Full-text Searching feature. Its sound like a neat feature if I could just get it to work.
Thanks in advance
Joe
View 2 Replies
View Related
Mar 20, 2007
I've assumed responsibilty for a sqlserver 2000 that has many databases and has only 1 database that uses full text searching. That 1 application now wishes for me to update the noise words file to remove the single characters.
As far as I can tell, no other application is using the full text searching since I don't see any catalogs created for any of them.
So, if no other databases and applications in this sqlserver have catalogs created for them, can I safely assume that changing the noise words file will not impact any of them even if the other databases do have text fields in some tables?
View 2 Replies
View Related
Feb 5, 2004
Everyone seems to think this needs something elaborate:
CREATE PROCEDURE sp_FindText @text varchar(8000), @findtype varchar(1)='P' AS
SET NOCOUNT ON
IF @findtype='P' SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @text + '%', text)))-Len(Replace(SubString(text,1, PatIndex('%' + @text + '%', text)),char(13),''))+1 AS Line,
--PatIndex('%' + @text + '%', text) AS Position,
OBJECT_NAME(id) AS ProcName
FROM syscomments
WHERE text like '%' + @text + '%'
ORDER BY ProcName, Line
IF @findtype='C' EXEC('SELECT TABLE_NAME + ''.'' + COLUMN_NAME AS TableColumn FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ''%' + @text + '%'' ORDER BY TableColumn')
IF @findtype='T' EXEC('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''%' + @text + '%'' ORDER BY TABLE_NAME')
GO
It not only searches procedure and view definition text, it will also find tables, views, and column names:
EXEC sp_FindText 'myTable' --or-- EXEC sp_FindText 'myTable', 'P' --finds procedures/views containing 'myTable' in their definition/code
EXEC sp_FindText 'myTable', 'T' --finds tables/views containing 'myTable' in their name
EXEC sp_FindText 'myCol', 'C' --finds columns containing 'myCol' in their name
It's pretty easy to modify and extend, and probably doesn't need dynamic SQL either. It returns line numbers for views and procedures, but these can be incorrect if the procedure or view contains over 4000 characters of code. It does not search tables for values, it's strictly for finding object names.
View 2 Replies
View Related
Dec 3, 2005
Hi i am really new to full text searching, I have created a catalog using:
sp_fulltext_catalog 'textcatalog','create'
and now i want to add a table with:
sp_fulltext_table 'Product', 'create','textcatalog', 'ProductID'
where product is a table in my database and productID is the primary key to that table. The primary key cannot be null,
But i get an error:
'ProductID' is not a valid index to enforce a full-text search key. You must specify a unique, non-nullable, single-column index.
Can any one point me in the right direction?
Thanks in advance
Tuppers!
View 3 Replies
View Related
May 28, 2007
I am building a generic job site and well I have hit a speed bump. I need to store resumes in the database to be searched on. Well what is the best way to store these full text resumes? so that they can be easily searched on?
SQL 2005
View 2 Replies
View Related
Aug 3, 2007
Hi
I've used CONTAINS on a varchar field.
in SQL SERVER 2000
the query was "Select name from description where CONTAINS(name,' "donot*" ')
say if I search for "Select name from description where CONTAINS(name,' "donot a*" ')
---it doesn't return rows.
as might be it is seeing that 'a' as the starting letter of AND key word which is used in CONTAINS
but how to tell that it is my next letter in the search
Really I need more clarity on this...
Thanxs in advance
View 8 Replies
View Related
Oct 29, 2007
Maybe a totally newbie question - I need to find some text in SQL database, I have no idea in which table it may be. Can I do it through SQL Management Studio 2005 or do I need some other utility? What would you suggest then?
Thanks.
View 6 Replies
View Related
Mar 1, 2007
Are there any plans to add full-text searching capability to SQLCE? I know it's available in SQL Server Express but it would still be useful for an embedded/desktop application -- SQLite recently added this.
View 1 Replies
View Related
Feb 27, 2008
Hi,
I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is;
//Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); ";
SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
//If UserName is null, display confirmation, else display errorif (UserName == null) ;
{UserNameCheckLabel.Text = "Username okay";
String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
}else;
{UserNameCheckLabel.Text = "That username is in use";
}
I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice!
Many thanks,
Chima
View 7 Replies
View Related
Feb 9, 2006
Hi everybody,
is it possible using SQL Server search for a word on each
table on each field?
Thanks
Fabio
View 6 Replies
View Related
Jan 12, 2006
I have the time of an event stored on each record as a datetime field.Itincludes the year,month,day, etc. Suppose my user wants to search the tablefor all events over the lunch hour, say between11am and 1pm. How do Iconstruct the SELECT query to peek into each datetime field and return onlythose records that satify the specified time range?Many thanks.
View 3 Replies
View Related
Feb 28, 2008
i have a query in SQL Server 2005 that uses a parameter like so:ALTER Procedure [dbo].[p_FullTextEquipmentSearch]
@search as nvarchar(50),
as
SELECT * FROM EquipmentView
where contains(*, @search);
what i want to so is add more parameters to the query so that the user can choose either 1 or many for the search can anyone help me on this
View 2 Replies
View Related
Mar 18, 2004
Hi,
I was wondering when is it worth using Full Text Searching as opposed to using LIKE? Also, since I will be using third party for hosting my site, generally, do hosts support Full Text Searching?
Thanks!
View 1 Replies
View Related
Jun 8, 2005
I'm wrtiting a local site search egine but I need to make use of functions like FREETEXTABLE
for instance. In order to use this I have to have the' Full Text Search
table' enabled for a table. Now the Microsoft Search Service is running
in MSDE; of that I'm sure. However when I try to enable the full text
searching on a table or database the option that I'm supposed to choose
is grayed out which means I can't select it. What's wrong? What do I
have to do to be able to use that option? I' short of time and would
appreciate n answer a.s.a.p. Thanks
View 3 Replies
View Related
Dec 23, 1998
I have been trying to write a query that will allow me to search through text fields. This is a problem because SQL doesn't let me use any functions on TEXT datatypes.
When I was using access I did it like this:
where upper(searchtext) like ('%SEARCHSTRING%')
However UPPER doesn't work on text fields (I want the search to be case insensitive)
I tried this:
where patindex(searchtext, '%SEARCHSTRING%') <> 0
but that is not case-insensitive...
Help me SQL gurus, you are my only hope
View 1 Replies
View Related
Dec 30, 2005
hello friends..
do indexing and full text searching needs cideamon.exe ?
cideamon exe eats much cpu.I want to disable it but I am afraid that disabling cideamon.exe affects mssql indexes to work.
thanks
View 3 Replies
View Related
Dec 29, 2005
I am very new to SQL and Access projects.
I have created the full text catalogs for my tables, so that I can search all the tables at the same time, from any of the fields in the catalogs.
I have created the following procedure:-
create procedure usp_full_text(@findtext varchar(255))
as
select set_id as URN, input_date as date_of_record from set_records where freetext (*, @findtext)
There are a few union selects under this, but
If I search for perhapse 'SMITH' from within access, then this will bring back all the records which have smith in them somewhere, which is good, however if I repeat the search for 'smit' then I get no records returned.
How can I change the code to find the parts of words, or string of charactors, and how can I set this to do a soundex search?
Hopefully all this is possible, i just need pointers on how to achieve this, or I could be sat there for days.
Many thanks for any help you can offer.
View 1 Replies
View Related
Jan 12, 2006
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
View 2 Replies
View Related
