How To Perform Like Search (eg Like 'abc%') Using Contains
Aug 31, 2007
Hello,
i am using Full text search for my web application search SP.
i have used Contains for Exact search and Freetext for Any word kind of search.
now i want to perform Like search using Contains.
same behaviour which we can get using LIKE word. (e.g. Select * from tbl where field like 'abc%')
even i have tried using contains(title,'"*Documen*"') . but its not working.
I want to have a FromDateTextBox and a ToDateTextBox where the user can enter in dates (most likely in mm/dd/yy format, although intelligently handing other formats might be a plus). Then I want to use these dates as the basis for a WHERE clause like:<some sql...> WHERE start_date BETWEEN 'FromDateTextBox.Text' AND 'ToDateTextBox.Text' (Note this WHERE clause will be used as the basis for an SqlDataSource FilterExpression). 1. I believe the date strings need to be in the format 'yyyy-mm-dd' to search SQL server is this correct?2. What's a decent way to convert the strings from the textboxes to the required format?3. How can I avoid an SQL injection attack?
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
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
Sorry to ask a stupid question. I have SQL Server 2000 on SBS 2003. I can't find Perform.exe. Anybody know where it should be (I'm sure that you all do!),
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.%';
Hi,any body pls help me with the concept called transaction ?Where excatly we are doin this ? In the SQL or through c# codings.i have 3 stored procedures, where i am calling this one by one to perform a single operation in the client side.if any one fails, due to some exceptions. it should rollback and come to the initial state.... How to perform this ? any ideas..
/*Reset Identity on tables with identity column*/ exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'
-- City SET IDENTITY_INSERT City ON INSERT INTO Elbalazo.dbo.City ( [CityID] ,[CityName] ,[CountyID] ,[Active]) SELECT [CityID],[CityName],[CountyID],1 FROM [ElbalazoProduction].dbo.tbl_City SET IDENTITY_INSERT City OFF
-- State SET IDENTITY_INSERT [State] ON INSERT INTO Elbalazo.dbo.State ( [StateID] ,[State] ,[Active]) SELECT [StateID],[State],1 FROM [ElbalazoProduction].dbo.tbl_State SET IDENTITY_INSERT [State] OFF
-- NumberOfPeopleOption SET IDENTITY_INSERT NumberOfPeopleOption ON INSERT INTO [Elbalazo].[dbo].[NumberOfPeopleOption] ([NumberOfPeopleOptionID] ,[NumberOfPeopleNameOption] ,[Active]) SELECT [NumberOfPeopleID], [NumberOfPeopleName],1 FROM [ElbalazoProduction].dbo.tbl_NumberOfPeople SET IDENTITY_INSERT NumberOfPeopleOption OFF
-- DeliveryOption SET IDENTITY_INSERT DeliveryOption ON INSERT INTO [Elbalazo].[dbo].[DeliveryOption] ([DeliveryOptionID] ,[DeliveryOptionName] ,[Active]) SELECT [DeliveryOptionID], [DeliveryOptionName],1 FROM [ElbalazoProduction].dbo.tbl_DeliveryOption SET IDENTITY_INSERT DeliveryOption OFF
-- User SET IDENTITY_INSERT [User] ON INSERT INTO [Elbalazo].[dbo].[User] ([UserID] ,[FirstName] ,[LastName] ,[Address1] ,[Address2] ,[CityID] ,[StateID] ,[Zip] ,[PhoneAreaCode] ,[PhonePrefix] ,[PhoneSuffix] ,[Email] ,[CreateDate] ,[Active])
SELECT [CustomerID] ,[FirstName] ,[LastName] ,[AddressLine1] ,NULL ,[CityID] ,[StateID] ,[Zip] ,[PhoneAreaCode] ,[PhonePrefix] ,[PhoneSuffix] ,[EmailPrefix] + '@' + [EmailSuffix] ,[CreateDate] ,1 FROM [ElbalazoProduction].dbo.tbl_Customer SET IDENTITY_INSERT [User] OFF
-- EntreeOption SET IDENTITY_INSERT EntreeOption ON INSERT INTO [Elbalazo].[dbo].[EntreeOption] ([EntreeOptionID] ,[EntreeOptionName] ,[Active]) SELECT [EntreeOptionID] ,[EntreeOptionName] ,1 FROM [ElbalazoProduction].dbo.tbl_EntreeOption SET IDENTITY_INSERT EntreeOption OFF
-- CateringOrder SET IDENTITY_INSERT CateringOrder ON INSERT INTO [Elbalazo].[dbo].[CateringOrder] ([CateringOrderID] ,[UserID] ,[NumberOfPeopleID] ,[BeanOptionID] ,[TortillaOptionID] ,[CreateDate] ,[Notes] ,[EventDate] ,[DeliveryOptionID]) SELECT [CateringOrderID] ,[CustomerID] ,[NumberOfPeopleID] ,[BeanOptionID] ,[TortillaOptionID] ,[CreateDate] ,[Notes] ,[EventDate] ,[DeliveryOptionID] FROM [ElbalazoProduction].dbo.tbl_CateringOrder SET IDENTITY_INSERT CateringOrder OFF
-- CateringOrder_EntreeItem SET IDENTITY_INSERT CateringOrderEntreeItem ON INSERT INTO [Elbalazo].[dbo].[CateringOrderEntreeItem] ([CateringOrderEntreeItemID] ,[CateringOrderID] ,[EntreeItemID]) SELECT [CateringORder_EntreeItemID] ,[CateringOrderID] ,[EntreeItemID] FROM [ElbalazoProduction].dbo.tbl_CateringOrder_EntreeItem SET IDENTITY_INSERT CateringOrderEntreeItem OFF
select * from BeanOption select * from CateringItemIncluded select * from CateringOrder select * from CateringOrderEntreeItem select * from CateringOrderEntrees select * from City select * from Country select * from DeliveryOption select * from EntreeOption select * from NumberOfPeopleOption select * from [State] select * from [User]
Hi all, I hope you guys can help me with the following bit of T-SQL. I already have a solution but I really don't like it and I've been trying to find a simpler more elegant way of doing the same thing.
Firstly, let me present you with a brief explanation of what I am trying to do together with some sample data for you to play with and hopefully assist me in finding a better solution than the one I’ve come up with.
insert into #VehMake select 222, 'FORD' union all select 210, 'FORD (USA)' union all select 223, 'FORD (AUS)' union all select 269, 'HONDA' union all select 253, 'NISSAN' union all select 280, 'VOLKSWAGEN'
This contains various vehicle makes which I'm sure you'll recognise!
insert into #VehicleHistory (PersonId, VehMakeVehModel) select 1, 'FORD (USA) MUSTANG' union all select 2, 'HONDA CIVIC' union all select 3, 'NISAAN ALMERA' union all select 4, 'VOLKSWAGEN PASSAT'
As you can see, in the second table, the second column contains a string of the vehicle Make and Model in one string. What I need to do is to split the Make and Model in to separate columns with an update statement.
This seems easy enough with a simple LIKE comparison:
VehMakeVehModel like VehMake+' %'
....BUT if you notice, there are two records in the #VehMake table that are similar but not the same. These are the 'FORD (USA)' and 'FORD (AUS)'. The update statement would return two records from the #VehMake table when trying to match with the first record in my #VehicleHistory table.
As I said, I did come up with a solution but it seems over complicated and I have a feeling that there is a way of doing this with an update. Maybe use the LEN() function but I'm not sure.
Your help would be much appreciated.
BTW, once I've identified the correct Make, I can easily populate my model as all I have to do is use the replace function on VehMakeVehModel column and remove the matched make to get the full model name.
Hope that makes sense and thanks for any help in advance.
I am looking to perform a calculation and enter the reult into a field within my table. The fields that I need to base the calculation on are all in one table (SALARY). The fields are: SALARY and BASIC_HOURS and the result is to be entered into field HOURLY_RATE. The actualy calculation to be preformed is:
this is my code: 1 myconnection = new SqlConnection(kiemtraloi,chuoiketnoi); 2 mycommand.Parameters.Add(new SqlParameter("@mancc",SqlDbType.VarChar,10)); 3 mycommand.Parameters["@mancc"].Value = TextBox1.Text; 4 myconnection.Open(); 5 myreader = mycommand.ExecuteReader(); 6 if(myreader.HasRows) And when I click to start button, a error appear: NO overload for method 'sqlconnection' takes '2' argumentsI don't know what i must do :(, please help methx
My e-commerce site is currently running the following process when items are shipped from the manufacturer:1. Manufacturer sends a 2-column CSV to the retailer containing an Order Number and its Shipping Tracking Number.2. Through the web admin panel I've built, a retail staff member uploads the CSV to the server.3. In code, the CSV is parsed. The tracking number is saved to the database attached to the Order Number.4. After a tracking # is saved, each item in that order has its status updated to Shipped.5. The customer is sent an email using ASPEmail from Persits which contains their tracking #.The process seems to work without a problem so long as the CSV contains roughly 50 tracking #'s or so. The retailer has gotten insanely busy and wants to upload 3 or 4 thousand tracking #'s in a single CSV, but the process times out, even with large server timeout values being set in code. Is there a way to streamline the process to make this work more efficiently? I can provide the code if that helps.
Hi all, Lets say that i have 3 tables, they are already related together, information on each of the 3 tables and their relationship between eachother can be found below:I am using Microsoft SQL Server 2005 Database in Visual Web Developer 2008 using VB programming (ADO.NET)The 3 tables are as follows:
FloorRoomLocation
Fields inside each of the tables can be found below:
The Relationships between the 3 tables are as follows:
ID field in Floor table is referenced by the field named FloorID in the Room Table ID field in Floor table is referenced by the field named FloorID in the Location table
NOTE: Location Table serves only as an extension to Floor table thus they have a 1 to 1 relationship, due to My Projects Lead's instructions I am not allowed to consolidate the Floor Table and the Location Table as 1 whole table. Floor Table has a 1 to Many Relationship with the Room Table.
I Have already done the physical relationships between each of the 3 tables in the Database Diagram, in Visual Web Developer 2008 Express Edition under the Database Explorer
So i have created this form to accept user input:
this form is in a aspx page called AddRooms.aspx
Country Name:______________ (data sent to location table)
City Name:_________________ (data sent to location table)
Building Name:______________ (data sent to location table)
FloorID:[This is a DropDownList which is databinded to the Floor Table]
Room Name:_______________ (data sent to room table)
*Just imagine that the above is a form to accept user input
*With a hit of a submit button all data will be sent to their respective tables in the database
Now i know that i have to JOIN tables in my database in order for my application to work, but i have no experience performing JOIN, i need great guys like you to help me apply JOIN to the 3 tables mentioned above, do let me know if you have any doubts in what i am trying to do.
Do give me that code that i can either apply in the (VB environment Visual Web Developer 2008) and/or in Microsoft SQL Server 2005 environment
Just need the SQL Statment that i will need to construct to perform the JOIN i need to.
I want to build some paging functionality into my repeater (b4 you ask, datagrid not providing flexibility required for presentation).
I will have no problem with the VB logic but I will need to execute SQL that only returns results from x to y (e.g. results 21 thru 40 for page 2). I know I can do something like 'SELECT TOP 20 * FROM...', or something like it, for page 1. But, I'm not sure if it's possible to build SQL for the pages greater than 1. Any suggestions.
I had my IT dept. install sql server 2005 enterprise edition on a new windows server 2003. All other machines are running sql 2000 server on windows server 2000. I am a sa and local admin on all servers. I tried performing copy database, backup and restore and detach and attach to upgrade the sql server 2000 databases to 2005 and all fail. I followed all steps. I was imformed that IT installed sp2 for sql server 2005. It's like the servers dont talk to each other. Their all on the company's domain. I am wondering if something happen with the install but the IT dept. insist that everything went fine. It's strange that I can't perform a simple backup and restore it on the new server buy when I click on restore it doesn't let me browse to get the backup file on the 2000 server. I never had a problem in Sql 2000 with backup. Can it be the installation was corrupted somehow. It seems fine. I haven't created any new databases because I wanted to move the databases from sql 2000. Can anyone help me get a clue of what the problem is please?
Hi, Appreciated someone able to provide me some clu on how to perfom DBCC CHECKDB. Is it necessary to set the database to "Read only" before execute DBCC CHECKDB?
I had my IT dept. install sql server 2005 enterprise edition on a new windows server 2003. All other machines are running sql 2000 server on windows server 2000. I am a sa and local admin on all servers. I tried performing copy database, backup and restore and detach and attach to upgrade the sql server 2000 databases to 2005 and all fail. I followed all steps. I was imformed that IT installed sp2 for sql server 2005. It's like the servers dont talk to each other. Their all on the company's domain. I am wondering if something happen with the install but the IT dept. insist that everything went fine. It's strange that I can't perform a simple backup and restore it on the new server buy when I click on restore it doesn't let me browse to get the backup file on the 2000 server. I never had a problem in Sql 2000 with backup. Can it be the installation was corrupted somehow. It seems fine. I haven't created any new databases because I wanted to move the databases from sql 2000. Can anyone help me get a clue of what the problem is please?
hey guys--i need some expert advice!we have a Dell PE 2650 with 2GHz and 2MB RAM with 2000 Server. on it wehave about 6 MSSQL DB's. a couple of the DB's are the back-end for adecent sized web app (5-10 users on average, 20 users at most). manytimes, the cpu skyrockets on our db server when using the web app.sometimes things timeout, other times not. at this time, upgrading thehardware is not an option. aside from increasing the timeout periods, iwas wondering if windows 2003 server would utilize the processor better(even just a little bit?) enough to know the difference.should i attempt the upgrade, or is 2003 no more efficient withprocessor resources?thanks a bunch!
I'm working with ADO 2.8 en C++ with Visual Studio 2005. I want to perform a "select" in asynchronous mode. I don't really understand the logical of the recordset events. For example, I received a number of MoveComplete event higher than the number of rows in my recordset. It is really not clear for me ...
Does someone knows where I can find a a good example of C++ (or VB) code to manage select statements in asynchronous mode ?
Hi everyone! I am building a database in VC++2005 Express, connecting to SQL Server. I planed to add Delete function to a button, and when clicked, the data with the specific ID according to the IDTextBox->Text would be deleted from the table. However, nothing happened. I don't know what's wrong with my program.
I have two versions of the same dataflow component , i need to use the new version component in the packages created using the old version . How can i do this using the perform upgrade method .
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
