Hi All, I am tasked to create an MP3 Lyrics Search Tool, but here is the catch, the database content given are all in Chinese Characters. So my problem is as such: Currently, lets say I am looking for the lyrics of this singer and this song: �德伟 - �给我 The results returned will be only be the lyrics of ONLY this singer and this song: �德伟 - �给我 So, if user missed out any single chinese character IN BETWEEN like this: � - �, NO RESULTS WILL BE RETURNED AT ALL!!! This is because, the database/system reads �德伟 - �给我 as a SINGLE STRING!!! What I have to do now: MAKE THE DATABASE/READ THE WORDS �德伟 - �给我 NOT AS A SINGLE STRING, BUT CHARACTER BY CHARACTER �, 德, 伟, - , �, 给, 我!!! So that, even if user keyed in �, 德伟, �- � or even � - �给我, ALL RESULTS CONTAINING ANY OF THE CHARACTERS THAT USER HAVE ENTERED WILL BE SEARCHED, RETRIEVED FROM THE DATABASE AND RETURNED!!! This have been a rather pressing problem of mine, and I would really appreciate any help given!!! Thanks for all the help in advance!!!
I need to search through a database that has a large number of tables to see if each table has an index that is based on the primary key. I need this information in order to determine whether I can setup database replication or not. I believe all tables involved with a replication need to be indexed on the primary key. Does anyone know of a stored procedure or command file that will provide this information for me in a timely manner? I've used the sp_pkeys stored procedure but it is very time consuming running this against one table at a time.
Hi All!!! I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem: The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned. So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character. Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID 2 FROM CRPL 3 WHERE (Trans LIKE '%' + @Trans + '%') OR 4 (Name LIKE '%' + @Name + '%') OR 5 (Address1 LIKE '%' + @Address1 + '%') OR 6 (Address1T LIKE '%' + @Address1T + '%') OR 7 (Address2 LIKE '%' + @Address2 + '%') OR 8 (Address2T LIKE '%' + @Address2T + '%') OR 9 (City LIKE '%' + @City + '%') OR 10 (CityT LIKE '%' + @CityT + '%')
Hi all.I'm looking for a tool which should act like some kind of middleware/logical layer bewtween the SQL server and the webbased user interface.- It should be possible to easily create simple web forms (only datainput and output) without programming effort by "clicking" the fieldsand their order on the web mask within an admnistrative interface.- It should also be bossible to add "new fields" to the database,including field type, permissions, value lists etc. (excluding anybusiness logic) without programming effort by administration.I know, that most workflow tools or "Trouble Ticket Tools", based ondatabases have this feature to easily configure new masks and add newfields to the database, but i need it as an tool-unspecific layer forthe MS SQL Server.Thank you very much for your feedback, any ideas are welcome!Best regards, Heiko.
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.%';
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
hiis there any tool to write stored procedurei have long queries to write instead of that i want a tool or editor where i can click or drag and drop to write procedurei make my work to complete fasterif any please tell me the site to downloadthanx byeramesh
Hello,I'm looking for recommendations for SQL tools used for comparing schemas from different databases. I would like the tool to be able to compare schemas for at least two databases and list the difference in tables, sps, functions, and view. Even if the both schemas contain the same structures I would like to know what differences exist. Also, I would like to be able to type in a table field name and get a list of every place in the db that field is referenced. On another note, how do others go about maintaining a version history of db structures? Any tools available for this functionality? Currently, I use visual source safe for my app. code. I could place copies of my SPs in visual source safe and edit them within visual studio. I'm not sure how I would easily get the edited version into my sql database without having to create a script or copy the edited version back to the db. Any thoughts?Any information is appreciated.Oscar
Hi guys, Im trying to use Bulk copy to transform a csv file into a table
BULK INSERT [BULK_INSERT_TEST] FROM 'c:stock.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = 'N'
)
--*STOCK LOOKS LIKE THIS stockcode,barcode,qty 10002,"10002087",0
TABLE Looks like this
id, stock,barcode, qty
can anyone give any guidance on performing this - at present this doesn't seem to work. Worth mentioning id is autoincrementing. I would also need to strip quotes around the second field. Note. Stock file has a header - do I need to strip this to use BCP?
There are 4 columns in total, however some rows have missing values for Column 4, and but given there are no field separators (all columns have fixed width and data is truncated if needed), how should the format be written to define a row terminator to apply to Column 3 if Column 4 is null.
I'm in the process of delivering a SQL Server BI solution to a customer. I have the cube built, SSIS working and an SSRS package with several hundred reports. They would like to roll their own reports.
I've looked at ProClarity (or whatever MS calls it now) and Report Builder. Both seem to have their pros and cons.
I think Service Broker is the right tool for what I want to do but need confirmation. Here is what I want to do.
Our company sells small business consulting services. When a client signs up with us, we put a server at their site that runs our app (using SQL Server 2005). Once the server is setup at their site and operational we constantly send certain information from that server to our central corporate server. There could be thousands of records every day being sent from each of these servers to our corporate server. We're using webservices now to transfer this data from each server but I'm afraid we're reaching a point where webservices will no longer work because of the volume of data.
1) I assume that Service Broker was created, in part at least, to address these types of situations?
2) If #1 is correct, is there any step by step examples/instruction that shows me how to use Service Broker to communicate between two SQL Server databases on two different servers?
Hi, I'm not sure this is the right place to ask this question (please re-direct me if it isn't). I would like to learn about SubSonic tool:1. do you recommend this tool ? if so what are its best uses?2. is its license allows me to work with it for free for commercial uses? any license limitations I should be aware of for using it as a tool? thanks,
Hi all, I downloaded the StudentActivities sample app from http://technet.microsoft.com/en-us/library/ms345152.aspx When I want to go through this tutorial I encounter the following problem: Database 'C:WebSitesStudentActivitiesRegistryDataStudentActivities.mdf' cannot be upgraded because its non-release version (587) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.Could not open new database 'C:WebSitesStudentActivitiesRegistryDataStudentActivities.mdf'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 950) So attaching the DB seems to doesn't work. The reason for this is probably that this sample app src was written in an older version of Visual WebDeveloper Express 2005 +SQL Server Express 2005. Therefore I would like if someone can provide me with the sql src of this StudentActivities database. Then I can create the database myself from this. Or is there some tool which canconvert a mdf to sql? I hope someone can help me with this. Thanks in advance. GJ
