Search On Sqlserver
Nov 1, 2007
I'm new on sqlserver, but now i have a question, how could i find an exact string like "rouse" inside of a larger string that is in one table?, i'm studyng on one university in Costa Rica, but after a few lessons nobody had told my how to do that.
For example if i need to find the string 'rouse' that could be inside of 25 rows or more i know that if i use pathindex sql returns me in what row is but how put that instruction in a select or in a stored procedure.
If someone could help me i'll be very thankfull.
Sorry for my english i know it suck but here we don't have a lot of practice.
View 1 Replies
ADVERTISEMENT
Dec 31, 2004
Is there syntax that lets you look for all names that include ell in them? That is, in Oracle it would be:
select id from customers where firstname like(%ell%)
Or, is there regular expression support in the select's where clause?
thanks
View 1 Replies
View Related
Dec 11, 2007
I just implemented full text search on a table and it doesn't work like I expected. I am using a stored procedure to search a company name column.
CREATE PROCEDURE swsp_GetCompanyRecord
@companyName varchar(120)
as
begin
select recID, companyName
from TABLE
where contains (companyName, @companyName)
end
I use the stored procedure as follows:
swsp_GetCompanyRecord '"Reynolds Aluminum"' (Quotation marks inside single quote)
If I search for a company (e.g., Reynolds Aluminum), I get the following results:
Search Returns
Reynolds Reynolds Aluminum
Reynolds A Reynolds Aluminum
Reynolds Al nothing
Reynold nothing
Aluminum Reynolds Aluminum
I guess I expected this to work similar to 'LIKE' in that it would return the full company name if I gave it any portion of the name. Is there something wrong with the way I've implemented this?
View 1 Replies
View Related
Sep 21, 2007
Posted - 09/19/2007 : 05:19:21
I have setup full text search on a table. Everything works fine. Now I want to find documents that contain the next string: 'T-150/04'
The query will give zero results. Is there a way to find documents that contain these kind of strings? (slahses and minus)
I use the following query:
SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"T-150/04"' )
If I do this query:
SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"judge"' )
It will work fine. I know it has to do something with the interpunction things (slahs and minus), but I don't know what query may solve my problem.
Also a select * from containstable(jurisprudentie, text, '"T_150_04"' ) doesn't give me the proper result.
The minus sign and the slash are disturbing things. If i search for "150" or for "04" I will find the record, but I would like to speficically search for the exact string "T-150/04". Because these are references to case law issued by the court.
I hope someone can help me out.
Regards,
CBGE
View 3 Replies
View Related
Apr 25, 2006
Does anyone know how to how to performance case-insensitive search onXML data type in SQLServer 2005? Or I have to convert all the xml datato lower case before I store it?Thanks in advance.John
View 2 Replies
View Related
Aug 9, 2006
I'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
View 3 Replies
View Related
May 10, 2007
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
namespace Predict.Services
{
public static class Foo
{
public static string GetIntradayQuote(string symbol)
{
string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
request.Timeout = 1000;
HttpWebResponse response = (HttpWebResponse)(request.GetResponse());
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close();
response.Close();
return returnQuote;
}
}
}
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
Regards,
Skipper.
View 1 Replies
View Related
Sep 20, 2006
Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?„¢"
„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')
and select again voila i see
"Œ€„¢"
Does anyone have an idea?
Thanks
View 1 Replies
View Related
Apr 18, 2008
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
Thanks!!
View 1 Replies
View Related
Jul 29, 2005
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
View 2 Replies
View Related
Jun 14, 2006
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
View 1 Replies
View Related
Feb 9, 2006
Hello,
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
Thanks in advance,
TassosTS
View 1 Replies
View Related
Mar 29, 2008
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 Related
Jul 21, 2015
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;
[Code] ....
View 4 Replies
View Related
Nov 6, 2014
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?
View 0 Replies
View Related
Jul 24, 2007
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?
View 1 Replies
View Related
Mar 30, 2013
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]....
View 14 Replies
View Related
Dec 11, 2006
How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?
Should I something download?
View 6 Replies
View Related
Sep 14, 2004
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
View 1 Replies
View Related
Mar 25, 2015
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.
View 0 Replies
View Related
Jul 6, 2007
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.
View 7 Replies
View Related
Nov 1, 2015
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.%';
View 12 Replies
View Related
Jun 20, 2007
Hello people.
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Can someone help me find a solution to this?
View 1 Replies
View Related
Apr 18, 2007
Regarding KB935356, is there a "post" service pack 2 update for SQLServer Express?
Thanks.
View 7 Replies
View Related
Jul 8, 2006
Hello Expert!
I have 2 Database €“ Access & SQLServer(ver 7)
I need to Import Data TblShift from Access to SQLServer €“ using DTS I€™ve done this successfully!
Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)
In SQLServer, I have created table to store the date range as following:
TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006
How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?
Is this possible or any better solution for this?
TIA
Regards,
View 4 Replies
View Related
Mar 1, 2007
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 Related
Nov 22, 2007
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
View 6 Replies
View Related
Apr 2, 2004
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?
:)
View 6 Replies
View Related
Dec 6, 2011
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] ....
View 1 Replies
View Related
Jun 24, 2015
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.
View 2 Replies
View Related
Dec 29, 2005
Hi Friends,
Can some please let me know the differences between sqlserver 2000 and sqlserver 7.0
View 1 Replies
View Related
Jun 6, 2007
Hi,
I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query:
SELECT * FROM SYS.Objects. It gives me following error:
Invalid object name 'sys.objects'.
Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.
Why is this difference? What is the difference when I login in these 2 different ways.
Any help would be appreciated.
Thanks in advance.
Any help
View 1 Replies
View Related
Jan 24, 2006
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" ')
View 2 Replies
View Related
