what use reason of 'weighted-term' ?explain it.
SELECT ID, firstname, lastname
FROM [contain-1]
WHERE CONTAINS(firstname, 'ISABOUT(mohsen weight(.8),yaser weight(1.0))')
table [contain-1] information:
ID FIRSTNAME
1 mohsen
2 mohsen
3 yaser
4 mehdi
thanks,mohsen
Hello,
Although this SP intends to sorround a search text in double quotes, it seems that when called from Management Studio it throws a Syntax Error even before entering the SP.
createproc fts(@t nvarchar(1000)=null) as begin
select @t = '"' + @t + '"'
select @t
select * from dbo.products where CONTAINS(name, @t)
end
GO
exec fts @t = 'my product name'
GO
Msg 7630, Level 15, State 3, Procedure fts, Line 4
Syntax error near 'product' in the full-text search condition 'my product name'.
-------------------------
If I pass the string in double quotes I get a different error:
exec fts @t = '"my product name"'
Go
Msg 7630, Level 15, State 3, Procedure fts, Line 4
Syntax error near 'my' in the full-text search condition '""my product name""'.
Now, if I remove the quotes again and make the original call:
exec fts @t = 'my product name'
GO
-- Then it works fine.
Doing a :
dbcc freeproccache
GO
Shows the same behavior as outlined initially.
Hi,
SQL Server Data Mining comes with "Term extraction" and "Term Lookup" for phrase detection. Rather than using the GUI tool, how to utilize these two features in coding? Please assist! Thanks!
Mary
I am designing a ssis package,This is intends to mine text data(Data extracted from websites).
Term lookup/Term extraction has been used as tools for mining.
I have lookup terms defined with me for reference table,but the main problem lie in extracting the nearby text/number/charcters to these lookup terms during mining.
For example :
I found noun "Email" 200 (frequency score) times in my text,Now I want to extract nearby email address(this is also true for PhoneNumber,Address attributes also).so how can I achieve this with SSIS.
If u have some idea/suggestion to carry out this challenge with or without Term Extraction/Term Lookup,plz do write here.
HI
The below is the asp code pasted here to explain what I am trying to do. How to Sum the weight of the item group by itemcode which is passed as a parameter
Do While Not objRS.EOF
itmcode = objRS("SKU")
sSQL = "SELECT code, numpacks, weight FROM tblproducts WHERE code = '" & itmcode & "'"
Set rsNumpks = Conn.Execute(sSQL)
If Not rsNumpks.EOF Then
numpacks = CDbl(rsNumpks("Numpacks"))
weigh = CDbl(rsNumpks("Weight"))
End If
Set rsNumpks = Nothing
Loop
Hi all,
Does this paramater/metric exist in any of the DM Models in AS2005?
Thanks in advance.
Mark
Is there any way to retrieve the weight % of some values?
A quick example:
A 10 -> Result i want is 10 / 60 (60 is the SUM of A,B and C)
B 20 -> 20 /60
C 30 -> 30/60
Code for number of subscriptions
EVALUATE
SUMMARIZE('Subscriptions',
'Subscriptions Type'[DS_SUBS_TYPE],
"Number of Subscriptions ",'Subscriptions'[NR_SUBSCRIPTIONS]).
Is it possible to dynamically set the font weight (bold vs normal) for a matrix row label? I always get an invalid value message box from the expression editor. For example:
IIf(Inscope("matrix1_Label_Name"),IIf(First(Fields!Label_Bold.Value,"matrix1_Label_Name") = "y"),"Bold","Normal"),"Normal")
Something like the following:
label1 | xxx xxx xxx
label2 | xxx xxx xxx
label3 | xxx xxx xxx
label4 | xxx xxx xxx
Thanks
Hi friends,
I have a text box with n number of text.
I want to set the font weight of each text in the textbox dynamically..
For eg.. suppose the text of the textbox is "Hello Friends", then i need "Hello Friends" as output.
Is there any way to accomplish this in SQL Reporting Service.
Any help will be appreciated. Its critical.
Please help me out ASAP.
I'm trying to use CONTAINSTABLE to do a product search on a number of columns. I want matches on some columns to be weighted higher than for other columns.
It seems like there should be a way to do this with CONTAINSTABLE - I know I can search by multiple strings and weight those matches variably, but what I want is to vary the weight by which column a single term matched. What I have now is a series of UNION queries, which are quite slow:
===================
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 50 as theWeight
FROM CONTAINSTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 40 as theWeight
FROM FREETEXTTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 30 as theWeight
FROM FREETEXTTABLE (Product, MatchKeywords, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 20 as theWeight
FROM FREETEXTTABLE (Product, ShortDescription, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode
ORDER BY 'theWeight' DESC, m.ShortName
===================
What is the better way?
Sorry, is this possible?
select * from COLLABORATOR x inner join
containstable
(CollaborAtor,*,
' ISABOUT (
"hiv and Gender" weight (.8)
or
"hiv or Gender" weight (.2)
)
'
)
as KEY_TBL
on x.ColId = KEY_TBL.[KEY]
==
basically I want all rows with either string, but I want those with both to rank first.
Also, how can I deal with singular vs plurals of words..
Thanks.
Also when using cross validation, which descision tree should we choose?
Thanks very much
There are many references in the BOL for the term "ad hoc":
ad hoc query
ad hoc updates
ad hoc monitoring
ad hoc testing
ad hoc view
ad hoc name
...
What are these terms mean? I'm especially interested in the terms ad hoc query and ad hoc updates.
I've just started using the SSIS and i would like to know if it's possible to change or update the dictionary of the term extraction tool. That's important to me because i may have to look for words that don't exist in the defaut english dictionary of the tool.
Thanks.
hi brother
what is the use of the term lookup please give me the
example
regards
koti
Hi,
Can you search a column of a database table to find all the rows that have a wor in it?
example: I have a row that contains 'adventure st north', there are other columns in that table that are suppose to be the same but read 'adventure street N.' or 'adventure st. N.' or 'North Adventure st.'
could I search for rows that contain 'adventure' in the column searched (lets call it columnA). I tried:
select * from tbl_test where columnA LIKE 'adventure' and got no results.
what is the way to do this?
Thank-you,
Eric
I'm not really sure how to explain this, so please bear with me.
I have a SQL statement, such as:
SELECT TOP (10) FROM chartTracks
This works with SQL Server Express 2005, but when I moved my site over to work with a MSSQL Server 2000, the statement had to be changed in order for it to work:
SELECT TOP 10 FROM chartTracks
I was just wondering if there was a technical term for this, and if possible, the locations of anymore sourecs of information regarding the above?
I'm just writing a report and would like to include this, if possible. Thanks in advance!
Just learning full-text searching in SQL Server 2005 and have questions about the proximity term "near".
1. How near is near? Measured in characters, words, or whatever?
2. How do you know? Is this documented? Can't find it anywhere.
3. Can it be adjusted? at design time? at runtime?
I have used a program called Sonar which has powerful proximity options that allow the user to specify proximity in terms of words at runtime. Would like to be able to do that but can't find much on "near" in the documentation other than it seems to relative, provides for left and right nearness, and allows for chaining of multiple search terms.
Hello,
when I extract nouns from a text with the Term Extraction Transformation, the destination indeed keeps the correct nouns but they are in a random order.
Is it possible to keep the order of these nouns and maybe already keep double occurrences?
Kind regards, _Rodney_
Is there a way to perform term extraction on each row in a table, and have the term extraction denote the frequency of the term on a per row basis rather than a per table basis?
Otherwise, is there a way I can take extracted terms and apply a sql function that returns the occurance of that term in each row?
Example: Row 1 = 2 hits, row 3 = 5 hits, etc.
Hi guys,
I have a field called URL in my table. I want to get the SEARCH TERM from a given URL and create a report based on that information. I'm getting difficulties, because the URL have different format depending up on the search engine
that the users use to browse. Some of the search engines are "google",".excite.com", "search.msn.","search.netscape", "search.lycos", "altavista", "search.yahoo" and many more.
Examples of the URLs from google :
http://www.google.com/search?q=S26+Collet+Chuck&hl=en&client=firefox-a&rls=org.mozilla:en-USfficial&start=30&sa=N -- The search term is S26 Collet Chuck
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-02,GGLG:en&q=kt21+kia -- The search term is kt21 kia
http://www.google.com/search?hl=en&q=Slagger+burning+Tables -- The search term is Slagger burning Tables
Does anybody have a sql query or used a CLR functions to get the SEARCH TERM from different search engine (URL).
Thanks in advance.
THIS IS THE ONE.TXT FILE
Customer called to complain that the ice maker on her fridge has stopped working model XXYY-3
Door to refrigerator is coming off model XX-1
Ice maker is making a funny noise XXYY-3
Handle on fridge falling off model XXZ-1
Freezer is not getting cold enough XX-1
Ice maker grinding sound fredge XXYY-3
Customer asking how to get the ice maker to work model XXYY-3
Customer complaining about dent in side panel model XXZ-1
Dent in model XXZ-1
Customer wants to exchange because of dent in door model XXZ-1
Handle is wiggling model XXZ-1
Customer happy with us. Best fridge yet!
i created the table term_result(term_id varchar2(50)); ( termid ==xxyy-3 like)
now i want to find the no of times repeat the xxyy-3 posted queries
ERROR :
DT_NTXT OR DT_WSTR TYPES ONLY ALLOWS HERE ERROR I AM GETTING SO
Flat File Source-----------> Data Conversion --------->Term Lookup -------->Oledb data source
one.txt DT_stR I choosen error occur here
SO WHAT IS THE DATA TYPE I HAVE TO GIVE FOR THAT MATCHING LOOKUP
REGARDS
KOTI
Hi all,
I got a problem, i'd like to update my DB in production in terme of
design and data from the DB test. In other words, I added many tables
to my design.
Therefore, i'd have to update the real DB on the server. Is there a way to do so with MSSQL? without doing it manually.
Thanks,
for example : " server={0};database={1};trusted_connection=true;application name={3}"
The article SqlConnection.ConnectionString Property refer this term,but doesn't specify use.
I want to know whether it will affect sql server and how to find its effect
thanks
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, I have a question regarding calling sql table columns dynamically? workflow would go as:1. user enters search term into a textbox2. user checks a checkbox to search by column in sqldb (eg.. firstname or surname) pseudo sql would go like......SELECT +%column1(checkbox1.value)%+ OR +%column2(checkbox2.value)%+ OR +%column3(checkbox3.value)%+WHERE column1 = +%TextBox.Text%+ OR column2 = +%TextBox.Text%+ 3. display results in gridview my sql needs to improve greatly so any code insight(good book or link) would be terrific . thanks
View 10 Replies View RelatedHello,
I want to search a column with all the words deliminate by underscore. E.g. User_id, Community_name, author_id and etc.
It seems like freetext only deal with string with blank deliminator. How should I do the rull text search on column like this? Here is the code.declare @var varchar(2000)
set @var = 'id'
select [name], definition,version_code
from dbo.base
where freetext([name],@var)
thx
Why terms are not being corrected on a partial match? I thought this was the point of term based relation rules.
I am testing in an email address (nvarchar) field?
E.g. trying to correct @@ to @ and .con to .com (just to test)
And everything passes.
If I test with an entire value, the field is corrected.
E.g. 605688878@@qq.com corrects to 605688878@qq.com if I enter those exact values as term based relations.
Hi, I test the following sql statement, finding that an error ocurs:
Msg 7630, Level 15, State 2, Line 3
Syntax error near '"' in the full-text search condition '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfds'.
DECLARE @searchTerm NVARCHAR(40)
SET @searchTerm = '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf"';
SELECT [JobTitle], [JobDes], [OpenDate], j.[URLRef], c.[CompanyName], c.[URLRef], c.[URLSource]
FROM JobWanted AS j INNER JOIN
Company AS c ON c.CompanyID = j.CompanyID
WHERE CONTAINS((JobTitle, JobDes), @searchTerm)
It seems too lengthy string will cause an error for full-text engine. I find the sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf is truncated as shown in error message.
How to avoid this issue? Could I configre this limination?
Thanks in advance.
Ricky.
Hi,
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
Thanks in advance for any answer.