Variable In Search Criteria(URGENT)
May 31, 1999
dear friend,
the problem i am finding is moreover a concatenation problem
what i did is that have stored procedure which is accepting any character and i have to show all the records where this character is existing
it is like this only
********************************
name1 like '% @str %'
but then it is not treating the str as a variable and treating it as a string so if you have any solution please let me know early
please give the answer with example
waiting for reply
ashish bhatnagar
View 1 Replies
ADVERTISEMENT
Dec 4, 2013
I am very new to sql programming. I have a database with 15 columns. At this moment I do this to get all rows containing the year 2013:
WHERE DATEPART (yyyy, DownloadDate) = 2013 .
But I also want to add a criteria so that I can have all rows where DownloadDate contains 2013 AND WHERE IsRead(BIT) contains NULL. I tried this:
WHERE DATEPART(yyyy, DownloadDate) = 2013 AND IsRead = null .
But this gave me nothing!
View 3 Replies
View Related
Jul 30, 2007
I have four criterias in my .aspx page. They are "First Name, Last Name, Title, Year". I have a Book and an Author table. The Author table would contain all the author's information and the Book table contains all the books information such as title, publisher, subject, and so on. So here's what I'm trying to do.
I want to write a transaction statement that will query the four criterias above if the criteria textbox is not black. So for example, when the user click the submit button, all the four criteria fields are filled except Year. That means the query would search the Author and Book tables for "First Name, Last Name, Title" but not "Year" for any potential matches. I also wanted to use "Like" instead of "=" for a wider search.
Actually I'm trying to create a store procedure that will accept those four criterias and search the tables based on those criterias.
Any help is appreciated.
View 11 Replies
View Related
Feb 3, 2007
i am working for a Library Project. in that project i want to search Book by putting book content. in book content there could be single quatation
e.g William's. when ever i put single quatation my string type is terminated and my query is not executed successfully.
select Book_ID,Title,Auther from book_details where Contents like '%xyz%'
here xyz is my object name.
if i put William's in xyz it does not work.
plz tell me how to solve this problem.
Jasim...
New Delhi, INDIA
View 4 Replies
View Related
Mar 20, 2012
I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.
I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.
View 5 Replies
View Related
May 26, 2004
I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?
View 9 Replies
View Related
Jun 20, 2014
Say I have a query like
DECLARE @ID UNIQUEIDENTIFIER, @SOMEDATE DATE
SELECT * FROM myTable WHERE ID = @ID AND DATEFIELD=@SOMEDATE
I want to pass values to @ID and @SOMEDATE, such that it meets the WHERE criteria for all values in the respective fields.
What parameter value should I pass such that all values are selected? In the actual SP, I have uniqueidentifier, varchar and date parameters.
View 2 Replies
View Related
Oct 21, 2014
My selection criteria is as follows:
where content like '%EditLiveJava%'
or content like '% Sys__%' ESCAPE '_'
or content like '%<div class="row"/>%'
or content like '%<a href="" title=""%'
or content like '%cmsprod%'
or content like '%Error processing inline link%'
or content like '%see log for stack trace%'
I output the content field if the search is true but would like to also output which specific 'like' has been found.
Can I do this in the one pass or do I have to read the database separately for each condition?
View 3 Replies
View Related
Apr 7, 2015
I have a store procedure that search by Firstname and Lastname. I want it search by either both (Firstname and Lastname) or any of them. For example if only FirstName passes to it shows all the record with that Fistname. Currently I have to pass both Firstname and Lastname to my store proc to get the result.
This is my stor proc:
USE [CustomerPortal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CSA_Search_Customer_By_Name]
[Code] ....
View 4 Replies
View Related
Jul 23, 2005
I am trying to create a query that will show me who is phoning who in anorganisation from available Telephone Billing information. I am creating aMSAccess 2000 database with a few few tables, two of which are:TableMembers: (containg fields Refs, DateCreated, MembershipNo,OfficeLocation ...NB: Refs has a Primary Key - No Duplicates)TablePeople: (containing fields: Refs, Name, Addr, TelHome, TelWork,TelMobile & TelFax)TableTelBills: (containing fields: Refs, TelNo, DateCalled, Duration,TelType)I am trying to create a query that will use a simple searching criteria eg.,Like "*" [Enter the Tel No or part Tel No to search:] & "*"to search all the Tel fields in the TablePeople and TableTelBills (TelHome,TelWork, TelMobile, TelFax and TelNo) but am running in difficulties.I start by creating a query and adding the tables TablePeople andTableTelBills and TableMembers.I use the Refs from the Table Members as a base criteria but do not know howto create criteria that will search all Tel fields at once!I would appreciate any and all help people!Jan
View 2 Replies
View Related
Nov 5, 1999
I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric
or non-printable characters. For instance, if I have a table with a 20 character name column with names with
beginning ranges from A-Z, why doesn't the following return any rows:
select * from table where name < CHAR(126).
In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and
A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?
I'm assuming this has something to do with the default collation sequence being used which somehow does
not include characters outside the alphanumeric range. Any ideas ?
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
Feb 20, 2007
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
View 2 Replies
View Related
Jul 20, 2005
Hi All,Sorry if the subject line is too obscure -- I couldn't think of a wayof describing this request.I have a table that contains approximately 1 million records.I want to be able to be able to select the top x records out of thistable matching variable criteria.Pseudo table records:custid, category, segment1,1,12,1,13,1,14,1,15,1,26,1,27,1,28,1,29,2,110,2,111,2,112,2,113,2,214,2,215,2,216,2,217,2,318,2,319,2,320,2,3So, what I'm trying to do is return a recordset, for example, thatcontains the top 2 of each variation of category and segment.ie:1,1,12,1,15,1,26,1,29,2,110,2,113,2,214,2,217,2,318,2,3The only way I can think to achieve this is in a while statement,performing individual selects against each combination, feeding thewhere criteria by variables that I automatically increment.I can't help thinking there's a much more graceful way of achievingthis?If anyone can give me any insight into this I'd be incrediblyappreciative!Many thanks in advance!Much warmth,Murray
View 1 Replies
View Related
May 24, 2004
For example, consider the following queries:
DECLARE @SomeParam INT
SET @SomeParam = 44
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
Thanks.
View 3 Replies
View Related
Jan 14, 2008
Hi All,
I am tasked to create an MP3 Lyrics Search Tool, but here is the catch, the database content given are all in Chinese Characters.
So my problem is as such:
Currently, lets say I am looking for the lyrics of this singer and this song: �德伟 - �给我
The results returned will be only be the lyrics of ONLY this singer and this song: �德伟 - �给我
So, if user missed out any single chinese character IN BETWEEN like this: � - �, NO RESULTS WILL BE RETURNED AT ALL!!!
This is because, the database/system reads �德伟 - �给我 as a SINGLE STRING!!!
What I have to do now:
MAKE THE DATABASE/READ THE WORDS �德伟 - �给我 NOT AS A SINGLE STRING, BUT CHARACTER BY CHARACTER �, 德, 伟, - , �, 给, 我!!!
So that, even if user keyed in �, 德伟, �- � or even � - �给我, ALL RESULTS CONTAINING ANY OF THE CHARACTERS THAT USER HAVE ENTERED WILL BE SEARCHED, RETRIEVED FROM THE DATABASE AND RETURNED!!!
This have been a rather pressing problem of mine, and I would really appreciate any help given!!!
Thanks for all the help in advance!!!
View 4 Replies
View Related
Jan 23, 2008
Hi all,
I have a search function in my website, which is fully functionable.
But currently, this is the issue that I am having problem with.
For example:
I have only the name 'John' in my database.
When the user search for 'John Tan', no results is shown as there was no such name in the database
But if this happens, I want 'John' to be always returned and shown, no matter what 'John' the user search - E.g "john tan", John Loh", john wee"
So, how do I go about doing this? I have done some research on how to go about doing it, what I found useful was the .Replace() function. However, I can never predict in advance what user is going to key in.
Any help is welcome as this is a rather pressing problem of mine. Thanks for all the help in advance!!!
View 21 Replies
View Related
Jun 7, 2007
I have two simple tables:
CREATE TABLE summaries ( summaryId int identity, summary varchar)CREATE TABLE phrasesToSearchFor ( phrase varchar)
I want to do this:
SELECT summaryFROM summariesWHERE CONTAINS(summary, <any of the values in phrasesToSearchFor>)
I've tried every weird permutation of the query I can think of, but can't find a way to make it work. Can anybody help?
Bonus Question:
I've got a function dbo.getPhraseToSearchFor(userId) that returns a phrase depending on the value of userId. I want to get a result table of (userId, summary) where for every user, I use dbo.getPhraseToSearchFor(userId) to figure out what to search the full-text catalog for. The docs say you can use a scalar function anywhere a scalar value is expected, but apparently that doesn't apply in this case -- you can't do CONTAINS(summary, dbo.getPhraseToSearchFor(23))
View 1 Replies
View Related
Jan 14, 2008
Hi All!!!
I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem:
The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned.
So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character.
Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID
2 FROM CRPL
3 WHERE (Trans LIKE '%' + @Trans + '%') OR
4 (Name LIKE '%' + @Name + '%') OR
5 (Address1 LIKE '%' + @Address1 + '%') OR
6 (Address1T LIKE '%' + @Address1T + '%') OR
7 (Address2 LIKE '%' + @Address2 + '%') OR
8 (Address2T LIKE '%' + @Address2T + '%') OR
9 (City LIKE '%' + @City + '%') OR
10 (CityT LIKE '%' + @CityT + '%')
Thanks for all your help in advance!!!
View 6 Replies
View Related
Mar 5, 2015
I have created a kind of search function in my Excel sheet where the user can select multiple items from different listboxes.
To save the items I use an array. So in one case only 1 items off the array is filled, but another time maybe 3 items are filled in the array.
I use the array to search in a SQL database (this is string rva(i) in the code). But when the array is empty it results in an error.
This is what I have so far. (the rule at the bottom won't work also )
rc.Open "SELECT [Datum],[RvA_Nr], [RvA_Letter], [Afdeling], [EVAL], [Matrix], [Component], [AS3000], [AP04], [Rec] FROM dbo.QHSE_2ndline_history " _
& "WHERE [Afdeling] = '" & afd & "' AND [Datum] >= '" & datum1 & "' AND [Datum] <= '" & datum2 & "' AND " _
& "CASE WHEN '" & rva(1) & "' <> '' then [RvA_Nr] = '" & rva(1) & "' END AND" _
& "([Matrix] LIKE '%" & matrix(1) & "%' OR [Matrix] LIKE '%" & matrix(2) & "%' OR [Matrix] LIKE '%" & matrix(3) & "%' OR
[Code] .....
View 1 Replies
View Related
Dec 4, 2003
Hi,
I have a repeater control which I populate with search results from SQL Server.
But I can't figure out how to cope with users who submit multiple search items and still use my stored procedure. Is this possible or do you have to build the query with a StringBuilder and execute it manually?
I'm using a stored procedure with parameters:
input parameters <-- PageSize & CurrentPage
output parameter --> TotalRecords
Am using a temporary table to store all records before Select-ing those required for the particular page.
If I compose the query manually then I can't figure out how to get TotalRecords back as a return parameter. Would appreciate help on this one.
Am hoping that stored procedures can cope with an unknown number of parameters.
View 3 Replies
View Related
Jan 28, 2008
doing insert using this method Dim insertSQL As String insertSQL = "Insert into " & myDB & " (student_name, student_passport, student_rcnumber, " & _ "test_level, test_venue1, test_venue2, test_row, test_column, " & _ "student_sex, student_age, student_dob,student_country, student_state, " & _ "guardian_name, guardian_passport, guardian_relation, " & _ "guardian_address1, guardian_address2, guardian_postcode, " & _ "guardian_homephone, guardian_mobilephone, guardian_otherphone, " & _ "payment, remarks, student_att) " & _ "" & _ "Values(@student_name, @student_passport, @student_rcnumber, " & _ "@test_level, @test_venue1, @test_venue2, @test_row, @test_column, " & _ "@student_sex, @student_age, @student_dob,@student_country, @student_state, " & _ "@guardian_name, @guardian_passport, @guardian_relation, " & _ "@guardian_address1, @guardian_address2, @guardian_postcode, " & _ "@guardian_homephone, @guardian_mobilephone, @guardian_otherphone, " & _ "@payment, @remarks, @student_att)" Dim conn As New OleDbConnection(myNorthWind) Dim cmd As New OleDbCommand(insertSQL, conn) cmd.Parameters.AddWithValue("@student_name", txtName.Text.Trim) cmd.Parameters.AddWithValue("@student_passport", txtPassport.Text.Trim) cmd.Parameters.AddWithValue("@student_rcnumber", txtReceipt.Text.Trim) cmd.Parameters.AddWithValue("@test_level", txtTestLevel.Text) cmd.Parameters.AddWithValue("@test_venue1", txtVenue1.Text.Trim) cmd.Parameters.AddWithValue("@test_venue2", txtVenue2.Text.Trim) cmd.Parameters.AddWithValue("@test_row", dropAlpha.SelectedItem) cmd.Parameters.AddWithValue("@test_column", dropNumeric.SelectedItem) cmd.Parameters.AddWithValue("@student_sex", dropSex.SelectedItem) cmd.Parameters.AddWithValue("@student_age", dropAge.SelectedItem) '------------Assembly Date Format Dim dob As New Date dob = dropDay.SelectedItem & "/" & dropMonth.SelectedItem & "/" & dropYear.SelectedItem dob = String.Format("{0:MM/dd/yyyy}", dob) cmd.Parameters.AddWithValue("@student_dob", dob) '------------End Assembly cmd.Parameters.AddWithValue("@student_country", txtCountry.Text) cmd.Parameters.AddWithValue("@student_state", txtState.Text) cmd.Parameters.AddWithValue("@guardian_name", txtGdName.Text.Trim) cmd.Parameters.AddWithValue("@guardian_passport", txtGdPassport.Text.Trim) cmd.Parameters.AddWithValue("@guardian_relation", txtGdRelation.Text.Trim) cmd.Parameters.AddWithValue("@guardian_address1", txtAddress1.Text.Trim) cmd.Parameters.AddWithValue("@guardian_address2", txtAddress2.Text.Trim) cmd.Parameters.AddWithValue("@guardian_postcode", txtPostal.Text) cmd.Parameters.AddWithValue("@guardian_homephone", txtHome.Text) cmd.Parameters.AddWithValue("@guardian_mobilephone", txtMobile.Text) cmd.Parameters.AddWithValue("@guardian_otherphone", txtOther.Text) cmd.Parameters.AddWithValue("@payment", txtPayment.Text.Trim) cmd.Parameters.AddWithValue("@remarks", txtRemarks.Text.Trim) If rdbAbsent.Checked = True Then cmd.Parameters.AddWithValue("@student_att", 0) ElseIf rdbPresent.Checked = True Then cmd.Parameters.AddWithValue("@student_att", 1) End If conn.Open() cmd.ExecuteNonQuery() conn.Close()Then i got this error must declar scalar variable @student_name need some enlighten plzz T_T
View 7 Replies
View Related
Nov 15, 2007
I am extracting data from multiple schemas with same structure. I have a table which have all the schema listed.Using Execute Sql task and For each loop, I am reading that table and string the value in a variable.
It is like this
"Select TK_ID,TK_TOKEN from " + @[User::Column1] + ".Tokens"
Column 1 is schema name.
Now I create a DATA FLOW TASK which has a OLEDB Source and OLEDB destination.
Using the option " Sql Command from variable" when I select the variable.
Since the Column1 will be evaluated at runtime , it doesnt give me any output columns to map to OLEDB destiantion and this throwing error.
OLEDB source output contains no output columns, The layout Validation failed.
Please help asap, my project is stuck.
View 9 Replies
View Related
Feb 15, 2006
All,
I need to pass a variable to get source data from Oracle on OLE DB source. The variable is defined by an Execute SQL task (previous step) on the control flow and it is a package level variable.
In the data flow, OLE DB source using €˜SQL command from variable€™. The variable sql command works when it just: Select *from table
But I need the variable SQL command like this:
Select * from table where last_modified_date > ? - - variable1
the variable1 is defined by anther Execute SQL task at runtime
Can someone give some light on this?
Many thanks
View 1 Replies
View Related
Aug 13, 2007
How to assign multiple values to emp1 variable separated by comma
Ex: If ajay karthik vijay are employee names in emp table
then emp1= ajay,karthik, vijay
set emp1= (select employeename from emp) returns error
error: subquery returns morethan one value
View 4 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
Mar 29, 2008
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies
View Related
Jul 21, 2015
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)
create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;
[Code] ....
View 4 Replies
View Related
Nov 6, 2014
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
View 0 Replies
View Related
Jul 24, 2007
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...
so i thought create site search table, with the columns:
id, text, url
and to write every thing to this table.
but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...
what do you think?
View 1 Replies
View Related
Mar 30, 2013
I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed
--The Path variable is set to the bin folder for the Adobe iFilter.
--SQL Developer version 64 bit on both Windows 7 and Windows 8.
USE master;
GO
DROP DATABASE FileTableStudy;
GO
CREATE DATABASE FileTableStudy
ON PRIMARY
[code]....
View 14 Replies
View Related
Sep 27, 2000
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
Please help!!!
View 3 Replies
View Related
Dec 11, 2006
How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?
Should I something download?
View 6 Replies
View Related
