declare @sql nvarchar(1000)
set @sql='select * from YellowPages_Userdetails where city='''+@city + '''and (category like ''%' + @SearchWord + '%'' or subcategory like ''%' + @SearchWord + '%'') '
I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition.
The stereotype behavior I'm looking for is:
SELECT myField FROM myTable WHERE @mySearchCondition
Does any one have some good suggestion, code samples and/or links?
System.Web.Services.Protocols.SoapException: The TypeName field has a value that is not valid. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidElementException: The TypeName field has a value that is not valid. at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items) at Microsoft.ReportingServices.WebServer.ReportingService2010.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items)
The type appears to be correct. I've tried type of "Folder" and receive the same error.
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Although this SP intends to sorround a search text in double quotes, it seems that when called from Management Studio it throws a Syntax Error even before entering the SP.
createproc fts(@t nvarchar(1000)=null) as begin
select @t = '"' + @t + '"'
select @t
select * from dbo.products where CONTAINS(name, @t)
end
GO
exec fts @t = 'my product name'
GO
Msg 7630, Level 15, State 3, Procedure fts, Line 4
Syntax error near 'product' in the full-text search condition 'my product name'.
-------------------------
If I pass the string in double quotes I get a different error:
exec fts @t = '"my product name"'
Go
Msg 7630, Level 15, State 3, Procedure fts, Line 4
Syntax error near 'my' in the full-text search condition '""my product name""'.
Now, if I remove the quotes again and make the original call:
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS", otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST", otherwize return "NAME SUCCESSFULLY DELETED") I tried the following which doesn't work: =IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"), IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
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
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.%';
I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.
I also need to adjust rows, say mth and nth that meet condition , say y.
My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)
This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.
Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:
ID Int1 Type1 Ideal Rank by Type
1 1 1 1
2 1 1 2
3 2 1 3
4 3 1 4
5 5 1 5
6 8 2 1
7 13 1 6
8 21 1 7
9 34 1 8
10 55 2 2
11 89 1 9
12 144 1 10
13 233 1 11
14 377 1 12
15 610 1 13
16 987 2 3
17 1597 1 14
18 2584 1 15
19 4181 1 16
20 6765 1 17
The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...
I hope this posting is clear, albeit long.
Thanks in advance
Greg
PS The code:
USE
master
GO
CREATE DATABASE CertainRowsToChange
GO
USE CertainRowsToChange
GO
CREATE TABLE InitialisedValues
(
InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,
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
hi 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
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
Hi guys I need to create an "advanced search" which will allow the user to narrow down his results. Ideally I'd want him/her to use the same search criteria form for each iteration, with a checkbox called "Search within results" type of thing. Now what I was wondering if there was any existing literature on how to effectively do this. I have tried doing it just through SQL Statements but they are becoming very messy and large. Is it possible to do this by searching the initial dataset, returning dataset #2 and then if a 3rd "search within results" is done apply the search against dataset #2 and return dataset #3 etc? Many Thanks John
--set ANSI_NULLS ON --set QUOTED_IDENTIFIER ON --go -- -- --ALTER PROC [dbo].[spEventTagCloud] --as --BEGIN DECLARE @RECORDCOUNT INT; DECLARE @SearchString varchar(2000); DECLARE @QRY VARCHAR(2000); DECLARE @SE VARCHAR(2000); SELECT @RECORDCOUNT=COUNT(*) FROM TBEVENTS DECLARE @ST INT; SET @ST=1; CREATE TABLE #TEMP2 ( MYTAGS VARCHAR(2000) ) --CREATE TABLE #TEMP3 --( -- TAGCOUNT INT --) CREATE TABLE #TEMP1 ( STR1 VARCHAR(2000) ) WHILE @ST<@RECORDCOUNT BEGIN SET @QRY='SELECT TOP ' +CONVERT(VARCHAR,@ST)+' EVENTTAG FROM TBEVENTS' --PRINT @QRY INSERT INTO #TEMP1 EXEC (@QRY) SELECT @SEARCHSTRING=STR1 FROM #TEMP1 SET @ST=@ST+1 declare @i1 int; declare @i2 int; declare @MatchType int ; set @MatchType=0; declare @Word varchar(100); declare @Words table (Word varchar(100) not null); declare @WordCount as integer; DECLARE @TEMPWORD VARCHAR(2000); begin set nocount on if (@MatchType != 2) begin set @SearchString = ' ' + @SearchString + ','; --print 'Search String is :::: '+ @SearchString set @i1 = 1; while (@i1 != 0) begin set @i2=charindex(',', @SearchString, @i1+1) --print @i1 if (@i2 != 0) begin set @Word = rtrim(ltrim(substring(@SearchString, @i1+1, @i2-@i1)))
SET @TEMPWORD=@WORD; SET @TEMPWORD=REPLACE(@TEMPWORD,',','') INSERT INTO #TEMP2 SELECT @TEMPWORD --print 'Search WORD is :::: '+ @WORD
if @Word != '' insert into @Words select replace(@Word,',','') end set @i1 = @i2 end end else insert into @Words select ltrim(rtrim(@SearchString)) set @WordCount = (select count(*) from @Words) Declare @wordtemp varchar(2000); set @wordtemp=@word set @wordtemp=replace(@word,',','') --INSERT INTO #TEMP2 SELECT @WORDtemp END END SELECT mytags'Tag' , count(mytags)'Count' FROM #TEMP2 group by mytags ORDER BY [COUNT] DESC --SELECT * FROM #TEMP1 DROP TABLE #TEMP1 DROP TABLE #TEMP2 --DROP TABLE #TEMP3 --END
________________________________ THE ABOVE EXAMPLE FOR A TAG CLOUD MY TAGS ARE AS FOLLOW
EVENTTAG _________ ASP.NET, C#, VB.NET WELCOME TO ASP.NET ASP.NET BOOKS,C#.NET BOOKS
I WOULD LIKE TO SELECT ALL COLUMNS FROM MY TABLE SEARCH TAG IS ASP.NET THE FOLLOWING CODE WILL GENERATE AND SPLIT IT AS
TAG ____ ASP.NET c# VB.NET WELCOME TO ASP.NET ASP.NET BOOKS C#.NET BOOKS
HOW TO SEARCH AN EXACT MATCH AS ASP.NET THE ONLY ONE ROW
da = New Data.SqlClient.SqlDataAdapter("SELECT [Products].[Names], Count([ProductList].[Products]) AS [Total] FROM [Products] LEFT JOIN [ProductList] ON [ProductList].[Names] = [Products].[Names] GROUP BY [Products].[Names] ", strConnection) can we use a where condition in the statement.If so how can we use it.
Hi all,I'm building a DataSet on Visual Studio and don't know how to do a condition (if/else) with SQL... I have a search form, with a DropDownList and have 2 options in it: Search by Title or Search by Author. If the "Title" is selected, then the value is "title and if "Author" is selected, then the value is "author".Here is what I have right now for the DataSet, as seperated queries but I think I can combine them to be one single query 1.This will returns the songs that matches the title:SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK, LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BYFROM t_lyricsWHERE ((@LYRICS_TITLE IS NULL) OR (LYRICS_TITLE LIKE '%' + @LYRICS_TITLE + '%') OR (LYRICS_TITLE2 LIKE '%' + @LYRICS_TITLE + '%')) AND (@LYRICS_TYPE = 'title') 2. This returns the songs that matches the author: SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK, LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BY FROM t_lyrics
WHERE ((@LYRICS_AUTHOR IS NULL) OR (LYRICS_AUTHOR LIKE '%' + @LYRICS_AUTHOR + '%') OR (LYRICS_AUTHOR2 LIKE '%' + @LYRICS_AUTHOR + '%')) AND (@LYRICS_TYPE = 'author') This is very inefficient because I have 2 queries, and I need to build 2 ObjectDataSources as well as 2 different GridViews to display the results. I think we can do something likeSELECT .... ... FROM t_lyricsif (@LYRICS_TYPE = 'title') DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH TITLEelse if (@LYRICS_TYPE = 'author') DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH AUTHOR But I don't know how to write that in T-SQL.Any help would be greatly appreciated,Thank you very much,Kenny.
