Strip Out The From Alphabetized Search Results?
Jan 16, 2006
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.
Thanks in advance.
View 8 Replies
ADVERTISEMENT
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
Mar 28, 2007
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.
Im running Sql 2005 Developer Edition, with BIDS.
View 5 Replies
View Related
Mar 14, 2007
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
View 2 Replies
View Related
Sep 18, 2007
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); }
View 7 Replies
View Related
Dec 10, 2005
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%'
MMColParam 1 Request.Form("keywordSearch")
MMColParam2 2 Request.Form("location")
MMColParam3 3 Request.Form("category")
Mally.
View 4 Replies
View Related
Aug 24, 2005
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
View 20 Replies
View Related
Jul 20, 2005
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 )
View 1 Replies
View Related
Sep 19, 2006
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
View 3 Replies
View Related
Jul 3, 2007
I need to order my search results by relevance to the keywords entered by the user, how can i do that in sql ?
View 6 Replies
View Related
Jun 23, 2005
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.
<%@ Page Language="C#" Debug="true" EnableSessionState="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.IO" %>
<script runat="server">
void Page_Load(Object sender , EventArgs e)
{
//strings used to get values from basicSearch.aspx
string strProvince;
string strGender;
string strHeight;
string strBodyType;
string strLooks;
string strHairColor;
string strEyeColor;
string strEthnicity;
string strHomeLanguage;
string strRelStatus;
string strRelInterest;
string strHaveChildren;
string strWantChildren;
//strings used for storing results from search
string resUserName;
string resFirstName;
string resLastName;
string resUserPhoto;
string resProvince;
string resGender;
string resAge;
string resHeight;
string resBodyType;
string resLooks;
string resHairColor;
string resEyeColor;
string resEthnicity;
string resHomeLanguage;
string resRelStatus;
string resRelInterest;
string resHaveChildren;
string resWantChildren;
string resProfileHeading;
string resTextDescription;
string resTextDescription2 = "";// used for the actual display of the value
string strQuery; // to store concattenated search strings
string strInput; // to store textfile input while being read
StreamReader objStreamReader;
strProvince = Session["sessionProvince"].ToString();
strGender = Session["sessionGender"].ToString();
strHeight = Session["sessionHeight"].ToString();
strBodyType = Session["sessionBodyType"].ToString();
strLooks = Session["sessionLooks"].ToString();
strHairColor = Session["sessionHairColor"].ToString();
strEyeColor = Session["sessionEyeColor"].ToString();
strEthnicity = Session["sessionEthnicity"].ToString();
strHomeLanguage = Session["sessionHomeLanguage"].ToString();
strRelStatus = Session["sessionRelStatus"].ToString();
strRelInterest = Session["sessionRelInterest"].ToString();
strHaveChildren = Session["sessionHaveChildren"].ToString();
strWantChildren = Session["sessionWantChildren"].ToString();
strQuery = strProvince + " " +
strGender + " " + strHeight + " " + strBodyType + " " + strLooks + " " +
strHairColor + " " + strEyeColor + " " + strEthnicity + " " +
strHomeLanguage + " " +
strRelStatus + " " + strRelInterest + " " + strHaveChildren + " "
+ strWantChildren;
SqlConnection conPubs;
string strSearch;
SqlCommand cmdSearch;
SqlDataReader dtrSearch;
conPubs = new SqlConnection(
@"Server=THALIONTHALION;Integrated Security=SSPI;Database=DateGame" );
//retrieve the results from the db
strSearch = "SELECT * FROM
client," + "FREETEXTTABLE( client, * , @searchphrase ) searchTable "
+ "WHERE [KEY] = client.userName " + "ORDER BY RANK DESC ";
cmdSearch = new SqlCommand( strSearch, conPubs );
cmdSearch.Parameters.Add( "@searchphrase", strQuery );
conPubs.Open();
dtrSearch = cmdSearch.ExecuteReader();
//start display of results
lblResults.Text = "<table
width='100%' style='border-style:solid; border-width:thin;
border-color:#E1E2DC;' cellpadding='0' cellspacing='0'>";
while ( dtrSearch.Read())
{
//values read from the returned result set
resUserName = dtrSearch[ "userName" ].ToString();
resFirstName = dtrSearch[ "firstName" ].ToString();
resLastName = dtrSearch[ "lastName" ].ToString();
resUserPhoto = dtrSearch[ "userPhoto" ].ToString();
resProvince = dtrSearch[ "province" ].ToString();
resGender = dtrSearch[ "gender" ].ToString();
resAge = dtrSearch[ "age"
].ToString();
resHeight = dtrSearch[ "height" ].ToString();
resBodyType = dtrSearch[ "bodyType" ].ToString();
resLooks = dtrSearch[ "looks" ].ToString();
resHairColor = dtrSearch[ "hairColour" ].ToString();
resEyeColor = dtrSearch[ "eyeColour" ].ToString();
resEthnicity = dtrSearch[ "ethnicity" ].ToString();
resHomeLanguage = dtrSearch[ "homeLang" ].ToString();
resRelStatus = dtrSearch[ "relationshipStatus"
].ToString();
resRelInterest = dtrSearch[ "relationPreference" ].ToString();
resHaveChildren = dtrSearch[ "haveChildren" ].ToString();
resWantChildren = dtrSearch[ "wantChildren" ].ToString();
resProfileHeading = dtrSearch[ "profileHeading" ].ToString();
resTextDescription = dtrSearch[ "textDescription" ].ToString();
// read the text file's info into a variable for display
if (
File.Exists( MapPath( "text files" +"\" +resTextDescription ) ) )
{
objStreamReader = File.OpenText( MapPath( "text files" +"\"
+resTextDescription ) );
strInput = objStreamReader.ReadLine();
while ( strInput != null)
{
resTextDescription2 += strInput;
strInput = objStreamReader.ReadLine();
}
objStreamReader.Close();
}
else
{
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";
if ( resWantChildren == "want kids" )
resWantChildren = "Yes";
else
resWantChildren = "No";
// The writing of html to display the values calculated
lblResults.Text += "<tr><td width='16%'
bgcolor='#C7C9BE' class='text_bold'><div align='center'>" +
resUserName +
"</div><hr noshade class='hr_line'></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'
class='text_bold'><div align='center'>" + resProfileHeading +
"</div><hr noshade class='hr_line'></td><td
colspan='2'><div align='center'
class='page_headings'>%</div></td></tr>";
lblResults.Text += "<tr><td rowspan='15' valign='top'
bgcolor='#C7C9BE'><p align='center'><img src='images/" +
resGender + "' width='20' height='22'></p>" +
"<img src='photos/" + resUserPhoto + "' width='80'
height='88'><div align='center'></div></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'><p
class='text'><br></p><p class='text'>" +
resTextDescription2 + "</p><p
class='text_bold'> </p></td>";
lblResults.Text += "<td width='7%' rowspan='15'
valign='top'><img src='images/hotlist_1.jpg' alt='Add To
Favorites' width='34' height='32'></td>" +
"<td width='14%' rowspan='15' valign='top'><img
src='images/email_1.jpg' alt='Email this profile' width='42'
height='36'></td></tr>";
lblResults.Text += "<tr><td width='26%' bgcolor='#E1E2DC'
class='text_bold'>Location :</td><td width='37%'
bgcolor='#E1E2DC' class='text'>" + resProvince +
"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Age :</td><td bgcolor='#E1E2DC'
class='text'>" + resAge + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Height : </td><td bgcolor='#E1E2DC'
class='text'>" + resHeight + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Body Type : </td><td bgcolor='#E1E2DC'
class='text'>" + resBodyType + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Looks : </td><td bgcolor='#E1E2DC'
class='text'>"+ resLooks+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Hair Colour : </td><td bgcolor='#E1E2DC'
class='text'>"+resHairColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Eye Colour : </td><td bgcolor='#E1E2DC'
class='text'>"+resEyeColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Ethnicity : </td><td bgcolor='#E1E2DC'
class='text'>"+resEthnicity+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Home Language : </td><td
bgcolor='#E1E2DC'
class='text'>"+resHomeLanguage+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Relationship Status : </td><td
bgcolor='#E1E2DC'
class='text'>"+resRelStatus+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Realtionship Interest : </td><td
bgcolor='#E1E2DC'
class='text'>"+resRelInterest+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Have Children : </td><td
bgcolor='#E1E2DC'
class='text'>"+resHaveChildren+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Want Children : </td><td
bgcolor='#E1E2DC'
class='text'>"+resWantChildren+"</td></tr>";
lblResults.Text += "<tr><td colspan='2'
bgcolor='#E1E2DC'> </td></tr>";
lblResults.Text += "<tr><td
bgcolor='#497792'> </td><td colspan='2'
bgcolor='#678BA1'> </td><td colspan='2'
bgcolor='#678BA1'> </td></tr>";
lblResults.Text += "<tr><td> </td><td
colspan='2'> </td><td
colspan='2'> </td></tr>";
resTextDescription2 = "";
}
lblResults.Text += "</table>";
conPubs.Close();
}
void Button_Login(Object sender , ImageClickEventArgs e)
{
SqlConnection conClient;
string strSelect;
string strclientName;
SqlCommand cmdSelect;
//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 );
cmdSelect.Parameters.Add( "@username", txtNickName.Text );
cmdSelect.Parameters.Add( "@userpassword", txtPword.Text );
//open a connection to db
conClient.Open();
//check to see if it already exists
strclientName = System.Convert.ToString(cmdSelect.ExecuteScalar());
if (strclientName.ToLower() == txtNickName.Text.ToString().ToLower())
{
//Store user name as session variable
Session["sessionUserName"] = txtNickName.Text.ToString();
txtNickName.Text = "";
txtPword.Text = "";
}
else
lbl_invalid_login.Text = "Invalid login!";
conClient.Close();
}
</script>
View 1 Replies
View Related
Nov 14, 2006
hi.
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.
thanks.
View 1 Replies
View Related
Feb 10, 2004
I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)
I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.
I'm lost on both the search and the results code.
I'd like to have a drop down for the search like this:
<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>
Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>
Any ideas on how to make this work would be greatly appreciated.
View 3 Replies
View Related
Nov 2, 2007
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.
View 4 Replies
View Related
May 28, 2015
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?
View 4 Replies
View Related
Aug 1, 2007
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.
Thanks
View 5 Replies
View Related
Apr 11, 2007
Hello,
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!
View 1 Replies
View Related
Jul 10, 2007
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.
View 1 Replies
View Related
Jul 23, 2007
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!
View 2 Replies
View Related
Nov 10, 2007
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??
expected results.....:
check
keywords
View 1 Replies
View Related
Oct 28, 2015
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) <> ''
[code]....
View 2 Replies
View Related
Jul 10, 2007
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.
View 1 Replies
View Related
Aug 6, 2012
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
Why it is not returning results all the time.
View 1 Replies
View Related
May 7, 2013
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.
View 1 Replies
View Related
Oct 28, 2015
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.
View 3 Replies
View Related
Apr 19, 2014
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)
Query
DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);
SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');
SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result
[Code] ....
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
Jul 13, 2004
Can anyone tell me how I can strip certain chahrcters from a string
I know I can use replace, but i don't think this is appropriate for what I want to do
For example I have the string
declare @text varchar (100)
select @text = 'word1, word2 & word3'
if i do a replace on the string like this
select @text = 'replace(@text, ',', '')
select @text = 'replace(@text, '&', '')
I end up with the string
select @text = 'word1 word2 word3'
i.e. 2 spaces between word2 and word3
What i want the string to look like is :
select @text = 'word1 word2 word3'
Is there a way i can check for more than one space + characters ( / , &) in one go
many thanks
View 4 Replies
View Related
Feb 26, 2004
I don't know how the stars are attribuated
but I don't think I should have so many
It gives a false impression to new members
View 3 Replies
View Related
Sep 26, 2007
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 ('{', '}')
DECLARE@Pos1 INT,
@Pos2 INT
SELECT@Pos1 = MIN(Pos),
@Pos2 = MAX(Pos)
FROM@Stage
DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)
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
IF @@ROWCOUNT = 0
BREAK
DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)
UPDATE@Stage
SETPos = Pos - @Pos2 + @Pos1 - 1
WHEREPos > @Pos2
SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '')
END
SET@Pos1 = PATINDEX('%cf[0123456789][0123456789 ]%', @rtf)
WHILE @Pos1 > 0
SELECT@Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1),
@rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ''),
@Pos1 = PATINDEX('%cf[0123456789][0123456789 ]%', @rtf)
SELECT@rtf = REPLACE(@rtf, 'pard', ''),
@rtf = REPLACE(@rtf, 'par', ''),
@rtf = LEFT(@rtf, LEN(@rtf) - 1)
SELECT@rtf = REPLACE(@rtf, '0 ', ''),
@rtf = REPLACE(@rtf, ' ', '')
SELECT@rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '')
RETURN@rtf
ENDE 12°55'05.25"
N 56°04'39.16"
View 10 Replies
View Related
Mar 26, 2004
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 :
stringA, stringC, stringD, stringE
Any help is greatly appreciated.
Thank you
dave
View 9 Replies
View Related
Dec 4, 2000
I am trying to strip off 'XYZ' from column1 in table1 whenever it occurs
Any help appreciated
saad
View 4 Replies
View Related
Feb 17, 2004
I'm trying to find a way to strip text from a string. In the past (pre SQL Server) I would've used
LName: Left(NCBH!Name,InStr(1,NCBH!NAME,",",1)-1)
To strip the last name from a string like
Franks,George J
Apparently InStr is not a recognized function in SQL Server 2000. Or is it available but not in a view?
Any thoughts would be greatly appreciated.
View 2 Replies
View Related
