Filter Expression For Searching With All The Words

Oct 29, 2006

Using VWD I have created a search feature using the LIKE clause.
  The filter expression on my SQLDataSource allows the user to search the Description field of a database and yield a result that contains the exact word or phrase entered into a textbox.
  Assuming that the user enters more than one word, my understanding is that the search result is limited to database rows that contain the EXACT phrase (such as found in an advanced Google search using the “with the exact phrase” option).
  The current filter expression is: Description LIKE '%{0}%' For example, if “John Smith” is typed into the search textbox, the results will include a row with: 1.
  “John Smith is my neighbor”  but NOT a row with  2.
  “John is my neighbor.
  His last name is Smith”.
   How does one modify the filter expression so that the search result is like the Google “with all the words” search option, where the search results are limited to records in which all the words typed into the textbox are present but not necessarily in the EXACT continuous order?
  In the example above, BOTH Descriptions would be returned in the search results
 when “John Smith” in typed into the search textbox. Thanks for any help you can provide in helping me refine my search options.

View 3 Replies


ADVERTISEMENT

Searching Words

May 25, 2005

hi i  am working on sql server200.I m using  "LIKE" to search the records.There is freetexttable and containstable table also.just like to know the difference between them.Could anyone provide me a good link regarding this??Thanks

View 3 Replies View Related

Searching For Whole Words?

Dec 21, 2005

How can you search for the occurance of a whole word in a string? but not return any results that have the word as a substring.

For instance, if I search for the term 'scene' in a column. Then it will only return rows that have the word 'scene' and not those with the word 'scenery'. I've tried the following sql, but it relies on having text either side of the word as well. If the word 'scene' is on the begining or end of the cell then it is not returned.


SELECT Name, Description
FROM tblWine
WHERE Name LIKE '%[^a-zA-Z]scene[^a-zA-Z]%'
OR Description LIKE '%[^a-zA-Z]scene[^a-zA-Z]%'


Any ideas?

Goran

View 5 Replies View Related

Searching With Ignored Words

Oct 24, 2007

Hi,

Is it possible with SQL Server 2005 to include ignored words in a full-text search? For example, searching for "in force as of"? This gives the same results as searching for "force" only. I've tried to empty the ignored words list (noiseENG.txt), but this does not seem to have any effect.

Also we want to be able to search for strings such as "205/1305-2". Searching with punctuation characters in a query seems to be a problem.

What are the possibillities in SQL Server 2005 with regard to these problems?

View 1 Replies View Related

Searching For A List Of Words

Oct 21, 2005

I need to build a search function for my site. So there is a single text box for the users to type in their search string. I have been asked that I need to break the user's search string into separate words. So if the user enters: "This is my search query", I need to break it into: "This" "is" "my" "search" "query" and then search for all these words.- Can I break a string into separet words using SQL?- How do I remove funny / dangerous characters from the search string?- If I have to break the search string using a programming language, I would have to run the search query for each word. If I run the search query for each word, How do I combine the search result for the user. For instance, if I search for "my" and find some result, then search for "search" and find some results, how do I display a SINGLE search result to the user.thanks

View 1 Replies View Related

Efficiently Searching Multiple Words In A String

Feb 15, 2008

 Hi,I'd be interested in people's thoughts about the following.  A user on my site will be searching for a venue name, and that could officially include a sponsor which the user might not search for.  Now I am using the AutoCompleteDropdown from the AJAX Control Toolkit, so the user will start typing in a few characters and the results will be returned. I can generate the results from sql by doing a simple LIKE '%' + @searchTerm + '%' however, this fills me with great fear of table scans. At the moment, we'd be querying against a table of 5K records, but our application is very new.I'm thinking one option is to split the words into another table - a one to many relationship to hold each word of the venue.  The benefit of this would be that you could do a:LIKE @term + '%'but then I have the cost of the join. (And the added complexity which is not a major issue)Any thoughts/tips?Thanks!   

View 1 Replies View Related

Searching French Words In Server 2008

Feb 17, 2014

I have french word like "Services d'organisation de minaires"..and i want to search word in SQL SERVER 2008. but the main problem is SQL SERVER give an Error because of string break.Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'organisation'.

View 1 Replies View Related

Regular Expression For Whole Words

Feb 8, 2006

I am try to build a query which only matches whole words and so far I've got this.


Code:

SELECT *
FROM tblSearchWords
WHERE CorrectSpelling LIKE '%[^a-zA-Z0-9]blah[^a-zA-Z0-9]%'



This will return rows which contain the string 'blah' without any numeric or alphanumeric characters beside it. However it doesn't return the rows where 'blah' is either at the start or end of the string as it expects any character except a-zA-Z0-0.

Is there any way to accept string when there is nothing on either side as well?

Thanks,
Goran

View 1 Replies View Related

Filter Expression

Jul 23, 2005

HiI have some data that need to be filtered based on a SET of Id's.If it's about a single ID, then i would pass it as a parameter in astored procedure and use it within the the WHERE Clause, but here thoseID's are determined in run time and I can't simply create a a storedprocedure for an unknown amount of ID's.I looked into the SQL Server 8.0 Manual but had no examples how to usethe Function Filter.Generaly, how can filter some records based on a set of ID's?Best regards

View 1 Replies View Related

How Do I Use OR Condition In Table Filter Expression

Feb 11, 2008



Hai guys,

In my report I want to use OR condition instead of AND in Table Filter Expression. By default this option is disabled in VS2005 Pro. How do I enable this? or is there any other way to do this? Thanks in advance.

View 7 Replies View Related

Reporting Services :: Failed To Evaluate Filter Expression Of The Grouping

Sep 23, 2015

I am trying to add a filter and sorting to a matrix, within the matrix I have a column which works out the percentage and is as follows

=(Sum(Fields!Sum_Distinct_JobBilledExVAT.Value) + Lookup(Fields!ClientId.Value, Fields!JobClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfJobClientsFees") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfClientsFeesID")) / (Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_MatchedToOtherPost.Value, "ProfClientsMatch") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfClientsCC") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value,

[Code] ....

I tried enclosing the query in Abs( ), which I have done in the past, although in the past I wasn't using Lookup functions so I don't know if that is the reason it won't work.

View 6 Replies View Related

Can I Apply A Database Function Or Assembly Call In An Expression For Filter Data?

Mar 28, 2007

I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter -
Language Code = GetUserCulture()
Which translates to this in the database query (for us english) -
table.language_code = 'EN-us'
And of course I need it to look like this -
table.language_code = 'ENG'

I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this
Language Code = dbo.ConvertFcnIWrote(GetUserCulture())
Or how I would access the custom assembly in the filter expression.

Do you have a recommended implementation for this situation?

Thanks,
Toni Fielder

View 4 Replies View Related

Automatic Select Filter (something Like Global Table Filter)

Apr 15, 2008

Hello,

Here is my problem:


I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)

Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;

All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.

However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.

Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.

However, I don't know what is the easiest and fastest way to filter the tables. Example:

I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;

Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".

I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.

I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?


Thanks in advance.

Best regards,
Beroetz

View 5 Replies View Related

And/or Filter Field Not Enabled In The Group Filter Tab

Jan 26, 2006

Howdy,

I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.

PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?

The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)

View 14 Replies View Related

URGENT - My Error Or Bug? The Result Of The Expression Cannot Be Written To The Property. The Expression Was Evaluated, But

Feb 8, 2007

Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1


"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"

Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works

However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur

(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)

View 4 Replies View Related

Reporting Services :: Running Value Expression Within Lookup Expression In SSRS?

Oct 28, 2015

I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".

I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.

Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.

This is the lookup expression that I added for the 2nd column.

=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").

I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.

(processing): (SortExpression ++ m_context.ExpressionType)

View 7 Replies View Related

How To Use LIKE For 2 Or More Words

May 5, 2008

Hello i need to know hoy to use the LIKE operator to find results that contains 2 or more words.
================TABLE EXAMPLE======================
I HAVE A TABLE CALLED ITEMS

ITEMNAME
Good Bike
Good Mountain Bike
Klein Bike Mountain
===================================================

If i use SELECT ITEMNAME FROM ITEMS WHERE ITEMNAME LIKE '%Good Bike%' i only get:
Good Bike



What to code i need to write if i want to get that results for QUERY: "Good Bike" returns
Good Bike
Good Mountain Bike

View 6 Replies View Related

Wildcards In Words

Apr 28, 2005

Hi I'm using the full-text indexing on a table and I'm trying to implement a search where users can search for words and use wildcards themselves. However I'm working on a method so that can enter a wildcard in the middle of a word to get records where they are unsure of the spelling etc.
For instance, a search of 'Ste*en' should return results like 'Steven' and 'Stephen' etc. So if they are searching for word 'establishment' they can search for 'estab*ment' and it should return all the records using this query:
SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*ment"')
If I do a wildcard at the end e.g: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*"')
I get the results I am looking for. But the middle wildcard does not seem to work as expected even though it is the syntax used on MSDN and other SQL info sites.
Is there something I am not doing properly?

View 4 Replies View Related

Ignored Words Nightmare!

Jan 10, 2006

Hi There,
I've created a couple of search pages which look at sql server. whenever words or values like "?@~:&£^" etc, or words like for, the and so forth, the page the nasrty error page:
Execution of a full-text operation failed. A clause of the query contained only ignored words
Exception Details: System.Data.SqlClient.SqlException: Execution of a full-text operation failed. A clause of the query contained only ignored words.
In short: is there a way I can stop it doing this. It looks rather horrible. I've looked at form validation but cant find anything that seems to fit. I would imagine there is a simple solution, but I haven't been able to find it so far.
Many thanks
Stuart
 
 
 

View 2 Replies View Related

Number To Words

Jul 23, 2004

hi

I want to know how convert number to words or words to number

for example. if i give input 100, i have to get the output "one hundred"

is there any built in function available. need solution immediately.

regards
nlakka

View 5 Replies View Related

Numbers To Words

Aug 27, 2004

Sir,
is there is any method to convert numeric ecpression to words ie 110 to "one humdred ten"
Pawandeep Singh

View 2 Replies View Related

2-3 Words In Field Name

Dec 20, 2006

Can I define field names with more words in Access and SQL Server likefield: "Bus station" instead "BusStation" or "Bus_Station"? I have hadproblems because of this in VB6. Can I have problems in VB 2005 or C# 2005and SQL Server?

View 1 Replies View Related

Japanese Words

Jul 20, 2005

Hi,I'm trying to read a varchar(50) field writed in Japanese using thissentence:is = rset.getBinaryStream(num);at that sentence the JDBC driver shows the following error:java.sql.SQLException: [Microsoft][SQLServer 2000 Driver forJDBC]Unsupported data conversion.Does anybody know why?Thank you,--__________________________________________Emilio PerezJoin Bytes!SINERGIA TECNOLÓGICAC/ Eusebio Sempere 1, Entreplanta A30003 AlicanteTel. 965 136 191www.sinergiatec.com__________________________________________La información incluida en el presente correo electrónico es CONFIDENCIAL,siendo para el uso exclusivo del destinatario arriba mencionado. Si ustedlee este mensaje y no es el destinatario señalado, el empleado o el agenteresponsable de entregar el mensaje al destinatario, o ha recibido estacomunicación por error, le informamos que está totalmente prohibidacualquier divulgación, distribución o reproducción de esta comunicación, yle rogamos que nos lo notifique, nos devuelva el mensaje original a ladirección arriba mencionada y borre el mensaje. Gracias.

View 2 Replies View Related

Select First 10 Words

Aug 28, 2007

Can someone point me in the right direction to write a select query to return the first 10 whole words from a table?

For example, table "testtable" contains a field named "description" with value "here is some test data in order to select the first full ten words from."

The SELECT statement would return the value "here is some test data in order to select the".

Thanks in advance!

View 7 Replies View Related

Retrieve The 1st 15 Words Of A Field

Nov 29, 2004

Hello,

i need a select statement that will retrieve only the 1st 15 words of a text field.


is it possible?

Thanks.

View 3 Replies View Related

Unique Words Statement

Sep 22, 2004

Hello all. I've been pulling my hair out for the last few weeks trying to come up with a statement that will do what I want. I'm hoping someone can lend some help.

Basically I have a table of articles with titles. I want to go thru the titles and find out what words show up the most. For example, if I had these two article titles in two records:

Microsoft develops new software for NASA
NASA blames software problem on Microsoft

I would get the following results - the word and the number of times it appears:

Microsoft 2
NASA 2
Software 2

The statement should ignore those words that only appear once. It would be nice to skip static words like the, and, a, etc.. (Or words 3 characters or less)

I have fulltext query enabled on the table which works great for searching, but not for what I want it to do.

Any suggestions? Thanks in advance.

View 3 Replies View Related

Need Table Of Words/glossary/etc.

Mar 3, 2005

Hi!

I have a client who wants to have a function to have a system create a validation system for users who register for a system. It would email them a registration code, which they want to be two or three random words strewn together.

So, I'm looking for a big table of words - random, glossary terms, etc. Does anyone have anything like this? (A flat file that I can import would be fine.)

Thanks!

View 14 Replies View Related

How To Convert Numbers Into Words

Mar 1, 2006

hi could u help me in suggesting how to convert number given in Rs or in $ to words as normal we spell.. ther's is no limit it can be billions or millions.. thanks in advance..

----
chakri

View 14 Replies View Related

Counting No Of Words In A Column

Sep 13, 2006

Hi,


for some reason, i had to write a function to count the number of words in a particular column in a table. (pl find the attachment). i would like to know whether there is any other mechanism with which we can count the number of words in a particular column.

for example, if the column data is,'This Is A Test', the function, will return 4.
pl suggest any other efficient strategies to accomplish this


thanks

View 11 Replies View Related

Is There A Way To Have List Of NOT Words With Containstable?

Nov 6, 2007

This works when @searchString is used in containstable (provided searchString has value)...

set @searchStringNoneOfWords = 'not(Airplane)'
SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords

This does NOT work when @searchString is used in containstable...

set @searchStringNoneOfWords = 'not(Airplane)'
SET @searchString = @searchStringNoneOfWords

I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.

View 1 Replies View Related

How To Convert Rupees Into Words

Feb 23, 2008

hi ,
I want to create a function in sql server 2000 which can display the given input currency into words but in indian format eg , '12345678.50' should display as One crore twenty lacs fourty five thousand six hundred and seventy eight rs fifty paise only . Can anybody help pls

View 4 Replies View Related

Convert Int Values Into Words..??

Apr 15, 2004

Hi,

I'm doing an online invoice system with ASP and MS SQL.

So you got the price and total amount.

And let say the amount is $4000.00 , there is also a portion where the invoice needs to mention it in words, "Fourty Thousand"

Can it be "cast" ? "conver" ? or anything?
Or is there any workaround????

View 3 Replies View Related

Display Rupees In Words?

Aug 29, 2013

In the below query i need display the amount in words also. How to do that in sql server?

SELECT ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS[TOTAL_CONCESSION_GIVEN] FROM BILL_MASTER BM

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved