I am not aware of this if it exists in MS SQL server. But I need to return results in alphabetic order ignoring "The" if it's the first word of a title...
so for example title "The Cliffhanger" would be returned along with other titles that start with letter C, but "The" also must be returned as part of the title, but just ignored while alphabetizing.
I'm sure that I'm not the first one to ever need this and don't want to re-invent the wheel, so if you have any ideas as to the best way to accomplish this, help me out.
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've built a report from a cube that I have had made. After selecting a few dimensions, the columns will be showing a drill down action related to different dates. Problem is, when you preview the report, the dates get alphabetized; they don't show up in an order like dates, days should.
ex: monday, friday, thursday, tuesday, wednesday
or april, august, july, june, may
How can this be changed, or is it related to the dimensions in the way they were made? Possibly something from the tables then? If more information is needed, please specify.
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
Hello! I am using this code to search the database but for some reason it's not liking the parameter I put there. Here is my code that works great with no parameters: protected void getBusinessInfo() { DataSet ds; ds = SqlHelper.ExecuteDataset(connection, "spBusinessInfoSearch"); gvSearch.DataSource = ds; gvSearch.DataBind(); } protected void btnSearch_Click(object sender, EventArgs e) { getBusinessInfo(txtName.Text); } Here is what I put in the stored procedure: ALTER PROCEDURE dbo.spBusinessInfoSearch
ASSELECT * from vwUserprofileInfo where DisplayName like '%Eric%'
RETURN Now, when I used the same code with paramers nothing is returned. protected void getBusinessInfo(string param1) { DataSet ds; ds = SqlHelper.ExecuteDataset(connection, "spBusinessInfoSearch", new SqlParameter("@DisplayName", param1)); gvSearch.DataSource = ds; gvSearch.DataBind(); } protected void btnSearch_Click(object sender, EventArgs e) { getBusinessInfo(txtName.Text); }
Can anyone suggest whey I dont get any results with this search?
SQL reads:
SELECT SupplierName, Location, ShortDescription FROM Query1 WHERE 'TimberSpecies' LIKE '%MMColParam%' AND 'CategoryTable' LIKE '%MMColParam2%' AND 'Location' LIKE '%MMColParam3%'
Dear all, I have illustared with code and sample output data my requestin thsi post. I simply was expecting some results from my search "amdsocket a 32 bit cache 512 dell" that includes a logical AND for all thewords in that search.Since i assume that any word might be an item_name, item_key oritem_value, i included all in the search.Can any one tell me why i get 0 results?The sample output of my data should look like this:item_id item_nameitem_keyitem_value--------------------------------------------------1Gefore MX 440Size64 MB1Gefore MX 440Architecture64 Bit1Gefore MX 440AGP8x1Gefore MX 440ChipsetNvidia1Gefore MX 440VendorAsus2AMD 3200+ClassSocket A2AMD 3200+Speed2 GHz2AMD 3200+Architecture32 Bit2AMD 3200+Level 2 Cache512 KB2AMD 3200+VendorAMD3Dell P780Geometry17 Inch3Dell P780Screen TypeFlat3Dell P780Frequency60 Hz3Dell P780VendorDellHere is my scenario:create table item_table (item_id int identity (1,1) not null primarykey, item_name varchar (50) not null)gocreate table details_table (item_id int not null, item_key varchar(50), item_value varchar (50))goalter table details_table add foreign key (item_id) referencesitem_tablegoinsert into item_table values ('Gefore MX 440')goinsert into item_table values ('AMD 3200+')goinsert into item_table values ('Dell P780')goinsert into details_table values (1,'Size', '64 MB')goinsert into details_table values (1,'Architecture', '64 Bit')goinsert into details_table values (1,'AGP', '8x')goinsert into details_table values (1,'Chipset', 'Nvidia')goinsert into details_table values (1,'Vendor', 'Asus')goinsert into details_table values (2,'Class', 'Socket A')goinsert into details_table values (2,'Speed', '2 GHz')goinsert into details_table values (2,'Architecture', '32 Bit')goinsert into details_table values (2,'Level 2 Cache', '512 KB')goinsert into details_table values (2,'Vendor', 'AMD')goinsert into details_table values (3,'Geometry', '17 Inch')goinsert into details_table values (3,'Screen Type', 'Flat')goinsert into details_table values (3,'Frequency', '60 Hz')goinsert into details_table values (3,'Vendor', 'Dell')gocreate view all_view asselect top 100 percent i.item_id, i.item_name, d.item_key, d.item_valuefrom item_table as i left outer join details_table as don i.item_id = d.item_idorder by i.item_id, i.item_name, d.item_key, d.item_valuego-- the complete search is "amd socket a 32 bit cache 512 dell"declare @search_key1 as varchar (50)declare @search_key2 as varchar (50)declare @search_key3 as varchar (50)declare @search_key4 as varchar (50)declare @search_key5 as varchar (50)declare @search_key6 as varchar (50)set @search_key1 = 'amd'set @search_key2 = 'socket a'set @search_key3 = '32 bit'set @search_key4 = 'cache'set @search_key5 = '512'set @search_key6 = 'dell'select distinct item_idfrom all_viewwhere((item_name like '%' + @search_key1 + '%') or (item_key like '%' +@search_key1 + '%') or (item_value like '%' + @search_key1 + '%'))and((item_name like '%' + @search_key2 + '%') or (item_key like '%' +@search_key2 + '%') or (item_value like '%' + @search_key2 + '%'))and((item_name like '%' + @search_key3 + '%') or (item_key like '%' +@search_key3 + '%') or (item_value like '%' + @search_key3 + '%'))and((item_name like '%' + @search_key4 + '%') or (item_key like '%' +@search_key4 + '%') or (item_value like '%' + @search_key4 + '%'))and((item_name like '%' + @search_key5 + '%') or (item_key like '%' +@search_key5 + '%') or (item_value like '%' + @search_key5 + '%'))and((item_name like '%' + @search_key6 + '%') or (item_key like '%' +@search_key6 + '%') or (item_value like '%' + @search_key6 + '%'))go----Best regards
I need some help with a query. I have a table with inventory that Ineed to allow customer searches on. Based on their search criteria, apreference level is calculated; the higher the preference level, thehigher on the order on the search results.The hard part is when the results are supposed to be limited to amaximum number of stores and items. Let's say that they only want tosee 3 stores and a max of 5 items per store. What needs to bereturned is the 3 stores with the best Preference and the 5 best itemsat each store.Create Table Inventory( StoreId int, ItemId int, Preference int )
Hi guys,As the title shows, i would like to learn how to return ranked results, and be able to order by relevance/rank. I am currently using SQL2000, though the final site may be going live with SQL2005. The data i am searching against is in tables, not Indexed files.I have no idea where to start......Does it have somthing to do with the amount of times the keywords appear? any help would be greatRegardsDarren
I'm busy writing a local site search engine that searches through a sql server database and I want to know how or what is the correct sql syntax to use in order to limit the amount of results a page loads at a time? The idea is obviously similar to something like google where you only see a certain amount of results first and then click at the botom for the next eg. 10 results.
The second question is how do I, after the first page with the first set of results that were shown, "clear" the second page of the previous html in order to show the next set of results? To give you an idea what my code looks like at the moment. Please don't kill me if the code is done a bit a lame, because I'm still learning.
resTextDescription2 = "myFile.txt does not exist!"; }
//determine whether male or female in order to display correct sign if ( resGender == "Male") resGender = "Male_sign_1.jpg"; else resGender = "Female_sign_1.jpg";
//determine whether 'want' and 'have' children and convert to correct words for display if ( resHaveChildren == "have kids" ) resHaveChildren = "Yes"; else resHaveChildren = "No";
//create a connection conClient = new SqlConnection( @"Server=THALIONTHALION;Integrated Security=SSPI;database=DateGame" );
//select statement strSelect = "Select userName From [client] Where userName=@username and userPassword=@userpassword"; cmdSelect = new SqlCommand( strSelect, conClient );
i know this is a bit of a shot in the dark but, does anybody know of a reasonably understandable way to rank XML node path results based on keyword search terms? seems like there are tons of uber-complex docs on the internet about this. i dont mind if it is a bit of a work-around, i am just looking for something that is reasonable non-complex that i can wrap my mind around. links etc apprecaited.
I want to break up a set of search results into small chunks. For instance, think about how Google displays a block of ten results out of the entire set. Selecting the top 10 is dead easy with "TOP 10"; how do I select the next 10?
Obviously one option would be to select the top 20, and programmatically discard the first 10, but surely there is a better way? I am doing this for a ASP.NET 2.0 application, and if I can retrieve just what I want, I can DataBind to a Repeater, and let ASP.NET do all the hard work of displaying the data.
Say I want to search for a range of account numbers but only which are active. After I set my field for A (active) this field shows in my results, I dont want it to.
In Access you can easily just uncheck that field in design view, but how do I do it in sql?
In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.
QA query based on the earlier script: Select * from StringTable WHERE (LongString = '' OR LongString IS NULL)
My script: The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:
-- if LongString column is varchar - run varchar update else nvarchar update If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin
-- update varchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)
END Else Begin
-- update nvarchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)
END
If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin
UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)
END Else Begin
-- update nvarchar ShortString UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)
END
My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.
My full-text search isn't working at all! I have a temporary table with full-text indexing enabled where files are scanned for social security numbers. If it has one, the user will see a message that it believes it's found a SSN and won't upload it. There is only ever one row in this table, as we overwrite the contents upon each upload.
I'm testing this search, and it doesn't work. The table has the following columns: attachemtId (int) - primary key fileContent (image) - contents of the file fileExtension (varchar) - extension of the file (this is always either ".pdf" or ".doc")
I created a .doc file that simply says "ssn", and then run the following query:
SELECT * FROM TempAttachment WHERE CONTAINS(fileContent,'ssn')
and nothing is returned! I tried the same thing with a .pdf file, and same results.
I'm not sure if this is related, but earlier I had this issue where I had to reset permissions for the directory. I've tried removing the full-text index and adding it again, but that didn't do anything. I also checked error logs on the server, and there were no messages. Any help would be appreciated! Thank you!
Hi, I was wondering if any SQL Server gurus out there could help me...I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.
I'd like to get some ideas for the following: I am writing a quick mini-application that searches for records in a database, which is easy enough. However, if the search term comes up empty, I need to return 10 records before the positon the search term would be in if it existed, and 10 records after. (Obviously the results are ordered on the search term column) So for example, if I am searching on "Microsoft", and it doesn't exist in my table, I need to return the 10 records that come before Microsoft alphabetically, and then the 10 that come after it. I have a SP that does this, but it is pretty messy and I'd like to see if anyone else had some ideas that might be better. Thanks!
Hi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long.
eg. CREATE TABLE [dbo].[tPopupKeywords]( [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
INSERT INTO dbo.tPopupKeywords(title, description) SELECT 'check', 'desc' UNION ALL SELECT 'for', 'desc' UNION ALL SELECT 'keywords', 'desc'
select dbo.tpopupkeywords.title where 'This is an example of a passed in string to check if any keywords are returned.' LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select??
I have created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
ALTER PROCEDURE [dbo].[RPT_Cost_copy] SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM INTO T1 FROM (SELECT a.meu, a.mep2, SUM(a.mest) as excst FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g. <META NAME="MS.LOCALE" CONTENT="ES"> and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)
However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g. DECLARE @SearchWord nvarchar(256) SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources. SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)
I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.
Any pointers / suggestions would be greatly appreciated. Cheers, Gavin.
We have a table that is Full Text Search index enabled on one column.This table has 200 lakhs of rows(20000000) . ContainsTable() function is searching data with in these 200 lakhs of rows(20000000), if any new rows are inserted then the ContainsTable is not going to search in these recent inserted rows.
We observed when we try for a data to search. it is returning the rows till the rows that are inserted date is less than 30th of march 2012. but not searching in the records that are created after April month , if even the data we are searching is available .
TableFulltextItemCount is around 2.2 crores.
Then we done rebuilt the FT catalog Index. then the TableFulltextItemCount became 0.Again we run the containstable query ,but still it is not getting results.
As the no of rows are very more . so i am not able to show the actual rows from which the data is not coming.
the below query gives 2 results that are from actual base table
HTML Code: select * from g_case_action_log where cas_details like '%235355%' and product_id = 38810
To search for the same above word using FTS,I have used the query as below
HTML Code: SELECT Distinct top 50 cal.case_id,cal.cas_details From g_case_action_log cal (READUNCOMMITTED)inner join containstable(es.g_case_action_log, cas_details, ' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key] Where cal.product_id = 38810 ORDER By cal.case_id DESC
I have attached one sql script file for your ref that contains create logic and index schema properties
I have a FullTextSQLQuery which I am trying to search a phrase(The Multi-part identifier) on full text indexed table. I am getting expected results on running the below sql query on QA machine and PreProduction servers, but not getting the same results on our development and production servres as even though same code running.
SELECT DISTINCT TOP 50 c.case_id,c.status_id,cal.cas_details FROM g_case_action_log cal (READUNCOMMITTED) INNER JOIN g_case c (READUNCOMMITTED) ON (cal.case_id = c.case_id) INNER JOIN CONTAINSTABLE(es.g_case_action_log, cas_details, ' "The multi-part identifier" OR "<br>The multi-part identifier" OR "The multi-part identifier<br>" ') as key_tbl ON cal.log_id = key_tbl.[key] ORDER By c.case_id DESC
We are using SqlServer 2008 R2 version on all servers.
We are running SQL 2014 SP1. We are using defined Full text indexes on several tables in the database. However, on one specific set of servers, a certain search will not return any data. This exact same search works on another set of servers built identically. The first responses I'm sure will be stop list, but I have dropped and recreated the FTI multiple times with different stop lists or no stop list at all and get the same results.
The specific word being searched on is YUM. If I change the value to YUMk, it actually returns, and if I change the data to TUM it returns, but YUM does not. This exact query is working on multiple other systems, so it seems to be something environmental, but I haven't been able to pinpoint it.
I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
This algorithm can be used to strip out HTML tags too. With reference to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89973 and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90000CREATE FUNCTIONdbo.fnParseRTF ( @rtf VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE@Stage TABLE ( Chr CHAR(1), Pos INT )
INSERT@Stage ( Chr, Pos ) SELECTSUBSTRING(@rtf, Number, 1), Number FROMmaster..spt_values WHEREType = 'p' AND SUBSTRING(@rtf, Number, 1) IN ('{', '}')
WHILE 1 = 1 BEGIN SELECT TOP 1@Pos1 = s1.Pos, @Pos2 = s2.Pos FROM@Stage AS s1 INNER JOIN@Stage AS s2 ON s2.Pos > s1.Pos WHEREs1.Chr = '{' AND s2.Chr = '}' ORDER BYs2.Pos - s1.Pos
I have a column of 5 comma-separated-value strings:
stringA, stringB, stringC, stringD, stringE
The strings are GUID's with the hyphen stripped and made all uppercase so they are completely random. I need to be able to remove any one of the strings including the comma, in a stored procedure and I am not sure how to accomplish this.
SELECT tickets FROM users WHERE CONTAINS (tickets, @ticket)
IF @@rowcount > 0 REMOVE STUFF HERE SET @valid = 1 ELSE SET @valid = 0
So if stringB gets passed in as @ticket then the new value in the column would be :
