Searching through a database with data stored as text places a huge load on
the server. I've seen some applications that use some sort of word indexing
that helps speed things up.
Is this the recommended strategy? And if so, how is this implemented and
are there any articles that detail this?
I'm having problems constructing an SQL statement to find records from a user entered string searching record. a record consists of a comma separated string. for example:
user entered string: charts
record: bars, graphs, charts
i've tried:
SELECT MI_FILENAME, MI_EXT, MI_KEYWORDS
FROM MEDIA_ITEMS
WHERE (MI_KEYWORDS IN ('bars'))
Could u offer any assistance please
I have a column Sports which contains a string.the string is comma delimited, so may contain e.g. 1,3,2,6,8,19 or 6,22,13 etc.What is (performance wise) the fastest way to select all the rows where the number 2 is in the Sports column.....?(I can't search on ",2," since "2" may also be at the start of the string....)
View 1 Replies View RelatedHi,Does anyone have any suggestions on an approach to speed this upplease?I need to check a given column in a given table to see whether thereare any characters within any of the records that fall outside of arange of valid ASCII characters.My problem is that of speed. The approach I have taken is to write afunction that iterates the string character by character and checks theASCII value at the current position. If it is deemed invalid I drop outof the function and return True. The calling procedure then knows tostop searching as the table column has already failed.Running down a table of million rows, can take up to an hour dependingon the datatype and level of data population.If I write the same thing in Access 2003 VBA and run it via ODBC ittakes a matter of minutes.I would like to find a set-based way of doing this maybe PATINDEX orsomething else creative but am stumped.Does anyone have any brilliant ideas?Thank you!
View 5 Replies View Relatedhihere is a problem:i have a databes with many, many tablesthe problem is that i dont know where 'abcd' string is (but it is for surein one of that table)is there any SELECT that could help me with finding this string in database?--greets
View 1 Replies View RelatedHi,I'd be interested in people's thoughts about the following. A user on my site will be searching for a venue name, and that could officially include a sponsor which the user might not search for. Now I am using the AutoCompleteDropdown from the AJAX Control Toolkit, so the user will start typing in a few characters and the results will be returned. I can generate the results from sql by doing a simple LIKE '%' + @searchTerm + '%' however, this fills me with great fear of table scans. At the moment, we'd be querying against a table of 5K records, but our application is very new.I'm thinking one option is to split the words into another table - a one to many relationship to hold each word of the venue. The benefit of this would be that you could do a:LIKE @term + '%'but then I have the cost of the join. (And the added complexity which is not a major issue)Any thoughts/tips?Thanks!
View 1 Replies View RelatedI have a text filed in my table.I have sample data looks like <<some status>> << 3/9/2008 10:00:45 PM>> <<personname>>Im interested in searching <<some status>>Â and <<personname>> together by skipping date in between so my query set should return status and same person name i m looking for.
View 11 Replies View RelatedMy problem is that i can't search a field that contains Asian characters (Korean in this case).
The table user_Access_tab have 2 keys:
access_id nVarchar(50)
user_id nVarchar(50)
The sql query below is sent through a oledbcommand to a sql server 2005 database.
"select access_id, access_right from user_Access_tab where user_id ='HQ001kimjo012007-05-07 ì˜¤ì „ 11:50:323401'"
It doesn't show any hits even thogh i know there is a number of matching records.
The question doesn't generate an answer in SQL server manager studio eigher.
If i change the datatype on user_id to Varchar(50) the id is presented (in the database) as:
HQ001kimjo012007-05-07 ?? 11:50:323401
Then the question works, but why doesn't it work with nVarChar(50)?
Regards Martin Jonsson
Hi,
I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.
Wildcard
Meaning
%
Any string of zero or more characters.
_
Any single character.
[ ]
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).
Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.
In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:
WHERE
Gift_Date NOT LIKE "####*"
After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:
WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'
using single quotes and the % wildcard instead of Access' double quotes and * wildcard.
Just putting this out there for anybody else that is new to SQL, like me.
Regards,
Patrick Briggs,
Pasadena, CA
I am trying to find all the email addresses with a " ._" I use '%._%' but it returns all records. What is the correct syntax? Also, is there a way to search for a field where the underscore is followed by a single alpha letter and then another underscore? like bla_A_bla or bla_Z_bla.thanksMilton
SELECT DISTINCT fname, lname, inet
FROM ocadbo.notes
where inet like '%._%'
Dear ASP.NET
How can I find records that contain a STRING from some (more than one) other fields ?
for example, I have:
Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"
Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)
How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?
Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)
Thanks in advance, Yovav.
Let say a user wants to search for the name Joe Soap
I have two column's in my table, firstname and lastname
So if I do:
Code:
SELECT firstname, lastname FROM table WHERE firstname LIKE '%Joe Soap%' OR lastname LIKE '%Joe Soap%'
it returns nothing! So do I have to split the string Joe Soap or something ?
my app use a registration table
StudReg
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stRegDt smalldatetime,
stRegNo bigint,
courseId smallint
)
the application registers the student details to a course.
each student gets a new registration no during registration.
the app should identify repeaters to a particular course by checking another
table RegHistory, which stores the details of student registrations for the previous 5 years.
RegHistory
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stPrevRegDt smalldatetime,
stPrevRegNo bigint,
courseId smallint
)
the application must search the RegHistory table and list out those students who are the repeaters.
the sample entries in the two tables are as follows
StudReg
stName stDOB stGuardianName stRegDt stRegNo courseid
-------------------------------------------------------------------------
abc 01/01/1979 def 20/11/2004 12345 1
def 01/01/1976 xyz 20/11/2004 12346 1
... ..... ... ...... .... ...
mno 24/18/1976 pqr 20/11/2004 12400 1
RegHistory
stName stDOB stGuardianName stPrevRegDt stPrevRegNo courseId
abc 01/01/1979 def 20/11/2001 2345 1
ghi 01/01/1976 xyz 20/11/2001 2346 1
... ..... ... ...... .... ...
dfg 24/18/1976 pqr 20/11/2001 2400 1
to determine whether a student is a repeater or not, we have to search for an exact match in RegHistory table (where the student name, guardian name and date of birth in both tables match with the corresponding entries in Registration table).
here is my question,
if there are 100,000 students registering in each academic year, we will have
500,000 records in RegHistory Table and 100,000 records in studReg table
if i start searching for a repeater, i guess i will have to loop through all records in studReg, for an exact match in RegHistory, which wil be a time consuming process.
is there any other options to search for repeaters ?
pl discuss
hi all,
I need help in selecting records from a table based on the given search criteria.
spec:
select * from table where col1='x' and col2='y'... and col6='q'
i may give any combination of column values.
I mean I can't provide 6 values in 'where' condition all the time i submit query.
help me with a stored proc which has 6 input parameters for the 6 columns.
Maybe a dumb question.. but is there a way to search all tables in a database for a particular word or phrase?
View 4 Replies View RelatedI am searching for an add-in to ssms which lets me choose which server to deploy my code.
For example, I have a complete statement with CREATE PROCEDURE and all code in my query window.
Now I want to run/deploy this code to many servers at once.
Instead of having to reconnect to all server (5), I want an add-in which have checkboxes for me to select.
Anyone heard of this?
E 12°55'05.25"
N 56°04'39.16"
Hi,I have come to point in my db design where I'm trying to figure whichis the best approach in making it generic (does this really matter?!?)Senario - I have a table called JOBS and this table contains fieldsuch as JobTitle, JobDescription, Salary etcI want to add to this table other attributes which are specific to acertain Job Industries.Solution - Add a join table for each type of industry containingattributes (db is now not generic) OR add a new table with a IndustryType field and a XML field containing the industry specificattributes.If I go the XML way will this just make it complex and slow to query?If not, what is the best way to query an XML field?Thanks,Jack
View 2 Replies View RelatedI am having a problem with seaching my tables. On my asp.net page ihave 3 text boxes. One for an ID and NAME and ADDRESS. I want to beable to search a table by using all or any combination of the 3 tosearch. But i cant get it right. i am using c# and sql server.any help ideas would be a great helpthanks in advanceructions
View 1 Replies View RelatedHow do we make a search in dbase where in the data you are searching has a " ' " for example Int'l Airport or other wildcards
Regards
i'm making a web page for a clinic.it needs to be able to search for patients by first name, surname, date of birth and patient number.i'm using visual web developer and i have my database, my data source and GridView grid.i want 4 text boxes for my first name, surname etc. when u click enter on any of them i want to retrieve all their data and display it in the gridview.at the moment i have one text box on the web page and through the "Configure data source" option on the grid view i can retrieve the specified data but for only this one item, e.g. SELECT * FROM [Patients] WHERE ([DOB] = @DOB). if i add another text box to my web page, and don't do anything to it, the query wont run. if i add and "AND" statement to the query, e.g. SELECT * FROM [Patients] WHERE (([DOB] = @DOB) AND ([FirstName] = @FirstName)), again it won'r run or return and data. any ideas on what i can do or where i'm going wrong. thanks
View 1 Replies View RelatedI have a table
GO
CREATE TABLE [dbo].[Speech] ( [SpeechId] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkSpeech_SpeechId PRIMARY KEY, [UniqueName] [varchar](52) NOT NULL, [NativeName] [nvarchar](52) NOT NULL, [Place] [nvarchar](52) NOT NULL, [Type] [smallint] NOT NULL, [LanguageId] [char](2) NOT NULL CONSTRAINT FkSpeech_LanguageId FOREIGN KEY (LanguageId) REFERENCES Language(LanguageId) ON UPDATE CASCADE ON DELETE CASCADE, [SpeakerId] [int] NOT NULL CONSTRAINT FkSpeech_SpeakerId FOREIGN KEY (SpeakerId) REFERENCES Speaker(SpeakerId) ON DELETE CASCADE, [IsFavorite] [bit] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDate] [datetime] NULL )
Now I want to search the Table Speech
Sometimes by : SpeechIdSometimes by : SpeakerIdSometimes by : LanguageIdSometimes by : SpeechId And LanguageIdSometimes by : SpeakerId And LanguageId
All can have conditions with IsVisible, IsFavorite and Type columns.
for example
I need all Speeches withany particular SpeakerId and LanguageIdwith IsVisible equals to trueand IsFvaorite No Matterand Type equals to Audio
For these type of queries I think the solution is
GO
CREATE PROCEDURE [dbo].[sprocGetSpeech]
@speechId int = NULL, @uniqueName varchar(52) = NULL, @nativeName nvarchar(52) = NULL, @place nvarchar(52) = NULL, @type smallint = NULL, @languageId char(2) = NULL, @speakerId int = NULL, @isFavorite bit = NULL, @isVisible bit = NULL
AS
SELECT SpeechId, UniqueName, NativeName, Place, Type, LanguageId, SpeakerId, IsFavorite, IsVisible, CreatedDate, ModifiedDate FROM Speech WHERE SpeechId = @speechId AND UniqueName = CASE WHEN @uniqueName IS NULL THEN [UniqueName] ELSE @uniqueName END AND NativeName = CASE WHEN @nativeName IS NULL THEN [NativeName] ELSE @NativeName END AND Place = CASE WHEN @place IS NULL THEN [Place] ELSE @place END AND Type = CASE WHEN @type IS NULL THEN [Type] ELSE @type END AND LanguageId = CASE WHEN @languageId IS NULL THEN [LanguageId] ELSE @languageId END AND SpeakerId = CASE WHEN @speakerId IS NULL THEN [SpeakerId] ELSE @speakerId END AND IsFavorite = CASE WHEN @isFavorite IS NULL THEN [IsFavorite] ELSE @isFavorite END AND IsVisible = CASE WHEN @isVisible IS NULL THEN [IsVisible] ELSE @isVisible END
Can anyone tell me?
Is it right way to do?Do you have any better solution?If my solution is better then Is there any performance loss with that query?
hello all..i have make a searching, but is not good. my code like that:Public Class getall Public Function getitem(ByVal id As String) As DataSet Dim con As SqlConnection = New SqlConnection("Data Source=BOYsqlexpress;Initial Catalog=GAMES;User ID=ha;Password=a") Dim ds As New DataSet() Dim adapter As New SqlDataAdapter("select * from [item] where name like '%" & id & "%'", con) Try con.Open() adapter.Fill(ds, "user") Return ds Catch ex As Exception Console.Write(ex.Message) Finally con.Close() con = Nothing End Try ' Next Return ds End Functionand class my item in database is containning dragon ball 3, counter strikeif i insert dragon, it can display dragon ball 3.but if i insert dragon 3, it not display dragon ball 3.it should display dragon ball 3 .how should i change my code?thx...
View 1 Replies View Relatedhello everyone
i need C# code in wh we sent "UserID" to table named "Users"
and if such "UserID" exists in "User" table we get his name from the table else we got message that no such record exists.
i have tried to write that code ,it works only if the value exists . in case of no value an exception is thrown.
please do sent me that c# code.
thanks for your consideration.
Hi All,
Just wanted to run this idea past u all before I have a go at it.
I have two tables A & B that are similar to the below
Table A
Name1 Name2 Name3
Tom Bill John
Gary Harry Eric
TableB
Name1 Name2 Name3
John Bill Tom
Tom Eric john
Leslie Philip Colin
What I wanted to do is see if the the records from tableA row 1 exist in tableB
As you can see they can appear in any order, partially or not at all.
What I propose to do is take tableA Name1 and see if it matches tableb row1 name 1 OR name 2 OR name 3 and if I find a match use a variable to assign the the value 1 (so I can then see if the match is full (score three) partial socre 1 and two or not at all (0)
Then I will need do the same for tableA row 2
Then goto row 2 of table a and start again.
Does this make sense? Are there beter ways of doing this.
I am using SQL server to do the searching....?
All comments appreciated and welcome.
rgs
Tonuy
hi i am working on sql server200.I m using "LIKE" to search the records.There is freetexttable and containstable table also.just like to know the difference between them.Could anyone provide me a good link regarding this??Thanks
View 3 Replies View RelatedHi 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.
i currently have a function and a storedpro in my sql database they are:CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(50), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(50), Rank INT, ImageALT VARCHAR(100), Artist VARCHAR(50))
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, 3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+ 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+ 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+ 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+ 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)) * CASE WHEN @Word2 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description) END * CASE WHEN @Word3 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description) END * CASE WHEN @Word4 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description) END * CASE WHEN @Word5 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) END AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, RankFROM #SearchedProductsWHERE Rank > 0 AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1 AND @PageNumber * @ProductsOnPageORDER BY Rank DESCand:CREATE FUNCTION dbo.WordCount(@Word VARCHAR(20),@Phrase VARCHAR(1000))RETURNS SMALLINTASBEGIN
/* If @Word or @Phrase is NULL the function returns 0 */IF @Word IS NULL OR @Phrase IS NULL RETURN 0
/* Calculate and store the SOUNDEX value of the word */DECLARE @SoundexWord CHAR(4)SELECT @SoundexWord = SOUNDEX(@Word)
/* Eliminate bogus characters from phrase */SELECT @Phrase = REPLACE(@Phrase, ',', ' ')SELECT @Phrase = REPLACE(@Phrase, '.', ' ')SELECT @Phrase = REPLACE(@Phrase, '!', ' ')SELECT @Phrase = REPLACE(@Phrase, '?', ' ')SELECT @Phrase = REPLACE(@Phrase, ';', ' ')SELECT @Phrase = REPLACE(@Phrase, '-', ' ')
/* Necesdbory because LEN doesn't calculate trailing spaces */SELECT @Phrase = RTRIM(@Phrase)
/* Check every word in the phrase */DECLARE @NextSpacePos SMALLINTDECLARE @ExtractedWord VARCHAR(20)DECLARE @Matches SMALLINT
SELECT @Matches = 0
WHILE LEN(@Phrase)>0 BEGIN SELECT @NextSpacePos = CHARINDEX(' ', @Phrase) IF @NextSpacePos = 0 BEGIN SELECT @ExtractedWord = @Phrase SELECT @Phrase='' END ELSE BEGIN SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1) SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos) END
IF @SoundexWord = SOUNDEX(@ExtractedWord) SELECT @Matches = @Matches + 1 END
/* Return the number of occurences of @Word in @Phrase */RETURN @MatchesENDmy database has many table but product is linkinked to albumsingledetails with productid in the albumsingledetails table, the the albumsingledetails table has the artistid in it which links to the artist table. I have tried searching for an artist but it does not find them!! can anyone see where i have gone wrong?
I want to search all of the employeeid #'s in the employee table for any mis-typed information. so if they put a . in the string i want to return that employee's info. as well as any letter a - z. I need to do this in sql and return it to a dataset for my crystal report. any ideas?
View 7 Replies View RelatedHello,How do I search through the gridview? Would I do this at the sqldatasourcelevel?I figured that I sould search with the datatable.select but how do I accessthe datatable of the sqldatasource?I am using ASP.NET 2.0 with VB.Thanks for your helpJ
View 2 Replies View RelatedHi:
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
hi ,
can any one help how to search on databases after user has entered some key words in random?
will be making front end in php.
I'm quite new to SQL, just wondering is there a way to search
the entire database (instead of a table using select) for something?
Say I create a new database to keep track of my CDs. A new table is
created in this database for each CD, and of course each row of this
table represents one track. Is there a way, either using tsql or through
the enterprise manager or query analyzer, to say list all songs that are
sung by a singer with the first name "John"?
I've tried the object finder in query analyzer, doesn't quite work.
I'm looking for something like
select * from *
where singerfirstname = 'John'
This of course doesn't work...
please help! Thanks so much in advance!
Kenneth in Canada
I'm trying to find out what modifies a certain table (yay for more debugging without documentation). Using some TSQL provided by a friend, I've searched through the stored procedures, and I've searched through the various pages in the web app, but the only references to the table are updates and selects (not inserts).
The table itself is intended as an archive for another table.
Unfortunatly, the documention for this web app is worthless, and the code has little to no comments to boot.
Is there any way to search through DTS packages aside from opening them each individually and looking at the parts?