Keyword Seach (Not Full Phrase) Parameterise Sql Statement
Apr 11, 2007
hi,
i want to do search by keywords for e.g "John Smith". should search for "John" and "Smith"
it is easy to do it using dynamic sql statement.
but i am using parameters sql.
this is my sql
"select * from emp_tbl where fname like '%' + @keyw + '%' or lname like '%' + @keyw + '%' "
the above sql will search by full phrase
how can i make it search each word in the phrase.
aslo, i am searching for 70-551 exam. to upgrade my mcad to mcts.
can anybody help.
View 9 Replies
ADVERTISEMENT
Oct 26, 2013
Due to limited number of characters..
no query cant be read by visual fox pro..due to error 36..
foxpro via mysql...here is the query..
SQLExec(nPayroll,"insert into py_abcdeeh(province,municipality,barangay,household_id,entry_id,lastname,firstname,middlename,ext_name,no_eli_for_educ_grant)
select m1.province,m1.municipality,m1.barangay,m1.household_id,m1.entry_id,m1.last_name,m1.first_name,m1.middle_name,m1.ext_name ,m2.count
[Code] ....
I did something like this but the query is not working anymore.. is there something wrong?? here is the query..
Query = ["insert into py_abcdeeh(province,municipality,barangay,household_id,entry_id,lastname,firstname,middlename,ext_name,no_eli_for_educ_grant)];
+[select m1.province,m1.municipality,m1.barangay,m1.household_id,m1.entry_id,m1.last_name,m1.first_name];
+[,m1.middle_name,m1.ext_name ,m2.count];
[Code] ....
VFP9.0 via MySQL 5.0
View 1 Replies
View Related
Apr 8, 2008
Hi,
When using FREETEXTTABLE the RANK returned seems to be partially based on keyword density. Has anyone come across a method of compensating for this keyword density so that good matches found in a lot of text and a small amount of text return the same RANK?
For an example look at the site I'm working on when someone searches for "whale watching" - http://www.yougodo.com/Search.aspx?ks=whale+watching - you can see that we are showing poor quality results at the top as our first sort order is based on RANK.
If we could remove the keyword density factor from RANK then this would allow second order sorting criteria to come in to play and move the more valuable results to the top.
Any help, pointers, advice would be greatly appreciated,
Gavin.
www.gavinharriss.com
View 1 Replies
View Related
Apr 8, 2015
I am searching for the key word 'Platform Customer Support' using full text search. My code is as below
Set @KeywordSearch = 'Platform Customer Support'
Select AA, BB, CC, DD from SM9..TableName A Right Outer Join SM9_Experiment..TableName C
On A.IncdTouchedGSF like '%' + C.SM9GroupName + '%'
Where
(
Contains(A.[Description], @KeyWordSearch)
And A.OpenTime Between @StartDate and @EndDate
And C.Classification = @GroupNameClassification
)
The code is throwing:
Msg 7630, Level 15, State 3, Line 46
Syntax error near 'Customer' in the full-text search condition 'Platform Customer Support.
View 2 Replies
View Related
Mar 5, 2007
In Classic ASP I did this:Dim sString, sArraysString = Request.Form ("txtQuery")sString = replace(sString, " ", "")sArray = Split(sString,",")strSQL = "SELECT * FROM Resources WHERE "For i=0 to UBound(sArray) strSQL = strSQL & "Description LIKE '%" & sArray(i) & "%' OR "Next strSQL = left(strSQL, (len(strSQL)-4))I have a .NET 2.0 web app with strongly typed datasets. I know how to create a method with a parameter, something like this:SELECT * FROM Resources WHERE Description=@DescriptionThe problem I have is that txtQuery can contain a number of strings to search for. Is there a better way to do this than what I'm trying?Diane
View 5 Replies
View Related
Jan 23, 2008
I have the following sql string in my asp.net, its meant to retreive a value based on a text box value being "like" a value from my database, however when i place a word in the textbox nothing happens, can someone please take a look at the statement and see if its well formed,
String sql = "SELECT fName FROM Customers WHERE fName LIKE " + "'" + fName.Text + "/%' OR PostCode= " + "'" + Postcode.Text + "'";
View 3 Replies
View Related
Jun 5, 2004
What is the SQL Server equivalent of the LIMIT keyword available in MySQL?
SELECT * FROM MyTable WHERE ID = @ID LIMIT 1
this SQL does not work? I get an error saying that there is an error at keyword LIMIT. When I remove LIMIT 1, it works...
is there anything like this in SQL Server?
View 4 Replies
View Related
Feb 28, 2008
Hello everyone, I'm working on a SQL statement that I "thought" worked fine until I noticed I was getting a duplicate row. Below is the SQL statement from the stored procedure: SELECT DISTINCT number AS 'RteNum', leg_orig AS 'Origin',
leg_dest AS 'Dest', AcEquipment.EquipmentDesc AS 'EquipType',
SUBSTRING(trailer_option, 1, 1) AS 'TrailerOption',
leg_depart_time_local AS 'DeptTime',
leg_arrive_time_local AS 'ArrTime',
dev.fnConvertEffectiveDaysToDaysOfWeek(SUBSTRING(leg_effective_local, 2 ,7)) AS 'EffectiveDays',
TruckEditor.EffectiveDays as 'NewEffectiveDays'
FROM lhif_prod
JOIN AcEquipment ON AcEquipment.EquipmentType = lhif_prod.Equipment_Type
LEFT JOIN dev.TruckEditor ON TruckEditor.Origin = lhif_prod.leg_orig AND TruckEditor.Dest = lhif_prod.leg_dest
AND TruckEditor.RouteNum = lhif_prod.number AND TruckEditor.DeptDate = lhif_prod.leg_depart_date_local
WHERE leg_depart_date_local BETWEEN @DateStart AND @DateEnd
AND Type_Code = 'T' AND leg_orig = @LocID
ORDER BY RteNum, Dest, DeptTime Here is what comes back from this query:ABE00 ABEA ABER CTV5 H 1855 1915 MTWT--- NULLABE01 ABEA ABER CTV5 H 1941 2001 MTWT--- NULLABE02 ABEA ABER CTV5 H 2045 2105 MTWTF-- NULLABE03 ABEA ABER CTV5 H 2059 2119 MTWTF-- NULLABE04 ABEA ABER CTV2.5 H 2245 2305 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- MT-TF--ABE12 ABEA ABER WALKIN H 2109 2124 MTWTF-- NULLEF038 ABEA EWRHB 53BULK H 0100 0245 -TWTFS- NULLEF085 ABEA EWRHA CTV5 H 1955 2140 MTWT--- NULLEF106 ABEA EWRHB CTV5 H 1901 2046 -----S- NULLEF140 ABEA ABER CTV5 H 0550 0610 M------ NULLEF166 ABEA EWRRA CTV5 H 2230 0010 MTWT--- NULLEF366 ABEA EWRRA CTV5 H 2230 0010 ----F-- NULLEF543 ABEA EWRRA CTV5 H 2200 2345 MTWTF-- NULL The 2 rows in bold are the issue right now. There should only be 1 row (the 2nd one where the last column is not null). I'm not sure why it returns both columns when I'm doing a join on there to add that last column. Can anyone help me out with this? I'm not very strong in SQL, so if I'm overlooking something, I'd appreciate any help you can provide. Thanks.
View 2 Replies
View Related
Nov 1, 2007
Following is the stored procedure iam trying to create.Here i am trying to
First create a table with the table name passed as parameter
Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows.
Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.
This table would be further used with cursor.
Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.
CREATE PROCEDURE [dbo].[sp_try]
@TempTable varchar(25)
AS
DECLARE @SQL nvarchar(MAX)
DECLARE @SQLINSERT nvarchar(MAX)
BEGIN
--create temp table
SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) +
'(
ContactName varchar (40) NOT NULL ,
ContactId varchar (30) NOT NULL ,
ContactrMessage varchar (100) NOT NULL,
)'
EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable
SELECT @sql= 'Select * from table'
SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable )
SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition
EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable
View 8 Replies
View Related
Aug 3, 2006
I am having this error when using execute query for CTE
Help will be appriciated
View 9 Replies
View Related
Oct 3, 2007
A column in my database contains phrases such as "Extreme Golf: The Showdown" or "Welcome to Happy Land". I need to write a search engine so that users could type in phrases such as "Golf Extreme Showdown" or "Happy Land" and the correct, or closest matched results will be returned. I don't need variations of words, just phrase keyword match based search. I know I could do this by using multiple LIKE %% statements OR'd together, but this would be too performance intensive. So, I have heard I should use charindex somehow to achieve this in a stored procedeure. Does anyone have any clue how to solve this problem? Thanks!
View 7 Replies
View Related
Jul 10, 2007
Due to a business rule change, I had to take what was 1 column in a table and split it off into a new table. Now I need to find every time that column is used in a SPROC and change those sprocs. Is there a way to sift through the sprocs to search for a "phrase" (the column name) -- other than reading through every one manually?
Thanks
Mark
View 11 Replies
View Related
Apr 21, 2008
I have a repository with about 10,000 records. These records are selectable by searching by author surname, this works fine, but. the powers that be would like the data to be searchable by phrase. The db lives on a commercial host where I'm unable to create full text search catalogues.
I'd like to be able to return all results that contain both terms, or either if someone types in (for example) Breast Cancer. The search will only select from the the one field which will contain complex phrases such as "Second-line treatment of postmenopausal women with advanced breast carcinoma"
Any help on creating a select query that will do this will be greatly appreciated...
View 1 Replies
View Related
Feb 9, 2008
I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.
I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?
I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.
Any thoughts are appreciated.
Thank you
View 1 Replies
View Related
Feb 9, 2008
I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.
I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?
I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.
Any thoughts are appreciated.
View 1 Replies
View Related
Jan 18, 2008
Hello all,
From what I've read, SQL Server is supposed to do a phrase match when you do a full text search that contains quoted literals. So, for example, if I did a full text search on the phrase "time out" and I put it in quotes, it's supposed to search for the full phrase "time out" and not just look for rows that contain the words "time" or "out." However, this isn't working for me.
Here is the query that I'm using :
SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC
What's it's doing is this : it's returning a bunch of rows that have the words "time" or "out" in the column called hed. It's also returning rows that have the full phrase "time out", but it's giving those rows the same rank as rows that only contain the word "time." In this case, that rank is 180.
Is there anything else I should be doing in my query, or is there some configuration option I should have turned on?
Thanks.
View 1 Replies
View Related
Jan 16, 2015
We compulsorily use NOLOCK in our scripts on reporting DB and development generally forget putting NOLOCKS or schema names with tables.
Creating a script to search for a missing word or a phrase in SQL Server script.
Our format is
<schema>.<table> <alias here> WITH NOLOCK <or alias here>
or
<schema>.<table> <alias here> NOLOCK <or alias here>
or
<schema>.<table> <alias here> (NOLOCK) <or alias here>
but generally developers write only <table> <alias>
View 4 Replies
View Related
Feb 27, 2015
I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:
Select CustomerID, customername,
from CustomerTable
where CONTAINS ((CustomerID), '"430*")
the query result only return all customer id which has space in front 430 or start with 430
ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"
but did not return customerID which doesn't has space in front of it
Ex: x430xxx
If I use LIKE '%430%', the result return are both have space in front and don't have space in front.
View 1 Replies
View Related
Apr 14, 2015
Select statement. In my database i am using the employee table. I need my first column to display your full name is 99 characters. so like if the employee is john smith it would display Your Full Name Is 9 characters (including the space).
View 2 Replies
View Related
Feb 14, 2008
question 1:
i found that database log file can contain more records after performing backup database statement.
for example:
i create a database and limit the log file to 2mb. then i create a table and insert data.
If i backup the database before i insert data , the database file can contain 192 records unitl the log file is full.
If i don't perform the 'backup database' statement.
The 'dbcc sqlperf(logspace)' indicate the utilization ratio is less than 40% after inserting 192 records
why?
I list my code:
Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
backup database db_test to disk='db_test.bak' --- if i don't execute this line, log file can contain a lot of record
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<192
begin
insert into db_test..table1 values(replicate('a',8000))
set @n=@n+1
end
question 2:
i create a database and limit the log file to 2mb. Then i create a table and insert data in an endless loop.
After the inserting operation executing for a while, the 9002 error occurs, indicate the log file for the database is full.
But the 'dbcc sqlperf(logspace)' command indicate the unilization ratio is low, and log_reuse_wait_desc in sys.database is 'CHECKPOINT'
And I can insert data , and i'm sure the state of log_use_wait_desc is 'CHECKPOINT'.
As i known, the checkpoint can't truncate log under full recovery model. Only the back log operation can truncate the transaction log.
So log is not full, why 9002 error is encounterd. and why the log_reuse_wait_desc return 'CHECKPOINT'?
I list my code:
Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<>-1
begin
insert into db_test..table1 values(replicate('a',8000))
end
any suggestions?
thanks in advance.
View 5 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
May 22, 2008
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
View 5 Replies
View Related
Nov 5, 2007
I have sql server 2000. I copied a database from one server to another. I have one table that has a full-text index. When I transferred over the database, the index still existed, but was not populated. I made sure the path for the file is pointing to a new correct location. I did "start full population". It only populated one entry @ 1MB. On the old server the index is 100MB with more than 3 million records.
I tried rebuilding, re-creating, and it all works, but when I run "start full population", it only populates 1 record. I double checked the table in question and it has over 3 million records and proper primary key.
How do I resolve this.
View 1 Replies
View Related
Oct 18, 2007
I am trying to pass several ids to use in a where clause. 1 For Each row as GridViewRow In gv_child.Rows
2 If row.RowType = DataControlRowType.DataRow Then
3 Dim chk as CheckBox = CType(row.FindControl("cb_Approve"),CheckBox)
4 If chk IsNot Nothing AndAlso chk.Checked Then
5 Dim id As Integer = CInt(CType(row.FindControl("lbl_id"),Label).Text)
6 ids &= "," & id
7 End If
8 End If
9 If ids.Length > 1 Then ids = ids.Substring(1)
10 'Submit to sql with ids as param...
11 Next
I am getting an exception. Error converting '38,39' to a column of datatype int. What am I doing wrong?
View 7 Replies
View Related
Mar 6, 2004
I need to retrieve records where the date is in between the current date and 4 days previous.
I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4
it doesn't work...
Can someone help out?
View 2 Replies
View Related
Dec 27, 2005
It's blue in SQL Studio but I can't find it in SQL Books on-line. Iwanted to use it as a field name but don't like using [].Is it reserved?
View 6 Replies
View Related
Nov 21, 2007
Hi guys,
Just a newbie question here.
Do we really need to use the GO keyword?
Thanks,
MeTitus
View 1 Replies
View Related
May 27, 2007
I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name.
At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?
View 2 Replies
View Related
Jan 31, 2008
Greetings, I am a php developer, and running a little bit out of deadline in a project. Can someone provide me with a VERY simple way to implement search by keyword in C#? I have already implemented a search page (according to firstname, lastname etc) that works on a drop down menu (where you have the option to choose seach by keyword) . So, I need to change something in my SQL query to make this work. I already knew from my SQL experience that the simplest and probably the SLOWEST and MOST UN EFFICIENT one was using LIKE. I don't mind using it but I can't since I will end up having something close to that: SELECT * FROM users WHERE keyword_entered LIKE @keyword; (or '@keyword) which does not work. however SELECT * FROM users WHERE keyword_entered LIKE 'somename%'; does work! I guess the trick is in putting the % after the keyword. ( I would have done that in php by putting the entered keyword in a string and than add to it % and pass it to the SQL query and I dunno how to do that in .NET)any ideas?
View 7 Replies
View Related
Apr 3, 2008
Hi everyone,
I have a few classes representing my data layer for my ASP.Net 2.0 Web Application. For example, UserDAL, DocumentDAL, etc... All these classes derive from BaseDAL, which stores the SqlConnection in it. The DAL classes access it through a property. The property is defined like that:
private SqlConnection connection;public SqlConnection Connection{get{ if (connection == null) connection = new SqlConnection(); if (connection.State != ConnectionState.Open) { connection.ConnectionString = ConnectionString; connection.Open(); } return connection;}}
The constructor of each DAL class is overloaded and takes a SqlConnection object. So if I need to access two DAL classes, I pass the connection of the first, to the second to prevent opening and closing the connection again and again. Here is a sample: UserDAL userDAL = new UserDAL();
userDAL.DoSomethingAndAccessConnectionProperty();
// Now userDAL.Connection is opened. Pass the connection to the constructor of DocumentDAL
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
So, if I do the following:using (UserDAL userDAL = new UserDAL())
{
userDAL.DoSomethingAndAccessConnectionProperty();
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
}
I assume that the connection is removed from memory, even if the DocumentDAL class has also used it. Am I right? I want to be sure that there will be no open connections.Thanks
View 6 Replies
View Related
Jan 9, 2004
I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.
Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.
I wanted to select distinct all of the individual words from each keyword field in all of the records.
Can this be done? What would the query look like?
I am looking for a list like:
bull
barrel
rodeo
western
cowboy
Any suggestions?
Thanks,
Rob
View 6 Replies
View Related
Mar 8, 2004
If I use DISTINCT isn't there a rule where it must be the first field selected? Also, there can only be one DISTINCT field in a query, correct?
ie,
SELECT DISTINCT fieldA, fieldB
FROM tableA
but not
SELECT fieldA, DISTINCT fieldB
FROM tableA
or
SELECT DISTINCT fieldA, DISTINCT fieldB
FROM tableA
thanks again, this is a great forum
ddave
View 6 Replies
View Related
Apr 5, 2008
hi guys i have a query that contains several table joins
when i run the query without select distinct x,y,z,w,.. or order by docno it takes around 20 second to finish execution, when i add select distinct x,y,z,w,.. or order by docno it ruturns the same result in just 2 seconds
is adding distict keyword or order by acts as an index for the query or what ?
.
.
.
.
here is my query :
SELECT distinct p.indocno,p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,J.DESCRIPTION AS [Section], p.SUBJECT
FROM dbo.TECHNICAL_MAIN p INNER JOIN
(SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j
FROM technical_main m
WHERE revision_no IN ('0', '1', '2')
GROUP BY char_field1, char_field2_ar, subcat_id) b
ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND p.REVISION_NO IN ('0', '1', '2')
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j
Good luck for all the folks
View 4 Replies
View Related
