I have a text field in a table that contains number along with chars.
Is there a way i can write a query to show all the fields that contains
just Numbers or Char in a field??
TBALE Example
COL1 : COL2(nvarchar)
---------------------------
100 345G01
200 123456789
300 GQ9220
Hi
Is there any function to seperate a sting (VARCHAR) into two based on a character in the string (e.g. '-').
In short is there "InStr" or "split" type function in T-SQL ? If not what is the alternative?
I have a column called "Range" (VarChar) in the table which has ranges like 1-14 or 15-49 etc.
I need to select records by compare a variable (numeric) against these ranges.
So for instace if the variable has 15 its in range 15-49.
I was thinking of spliting the range field into two so that comparison is possible.
Any suggestions?
thanks
Hi guys, first post here. It's been a few years since I've worked with ODBC, so I'm a bit rusty. I'm having trouble figuring out how to use a char* string as a parameter for a SELECT statement. I'm trying to compare the contents of a varchar column (with size=20) against the string.
Here's the code that I'm using:
char* arg;
SQLINTEGER stringSize = 32;
retcode = SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_VARCHAR,
20,
0,
arg,
stringSize,
&stringSize);
retcode = SQLPrepare(hstmt,
(SQLCHAR*) "SELECT * "
"FROM myTable "
"WHERE (stringColumn = '?')",
SQL_NTS);
retcode = SQLExecute(hstmt);
retcode = SQLFetch(hstmt);
When SQLFetch() runs, it returns 100 (SQL_NO_DATA). If I type the query manually into my Enterprise Manager console, I get several results, so I know the query is correct. Any idea what I might be doing wrong with the ODBC calls?
Thanks!
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)
SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'
SELECT @patfind = '%' + @find + '%'
UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)
SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'
SELECT @patfind = '%' + @find + '%'
UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind
Hi Everyone-
i woder if someone know Built in function to search the occurence of char in string in T-SQL
and iam not talking about the Index of the first occurence (e.g CHARINDEX )
but it is more generic
i want to send to the function to ask about the index the second or the third or N occurence of the char.
and i wonder if it is built in for performance reasons
example
getseachIndex("M|d|d","|",2)==> the result is 4
#1 This stored procedure can be used to search and replace substring in the char, nchar, varchar and nvarchar columns in all tables in the current database. You should pass the text value to search and the text value to replace. So, to replace all char, nchar, varchar and nvarchar columns which contain the substring 'John' with the substring 'Bill', you can use the following (in comparison with the SetTbColValues stored procedure, this stored procedure replace only substring, not the entire column's value):
EXEC replace_substring @search_value = 'John', @replace_value = 'Bill'
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 RelatedI have been working on a pretty ugly stored procedure recently, while debugging I added a char(10) to the end of each line of the SQL query so I could copy it to query analyzer(QA) and debug the SQL syntax output from of the stored procedure.
It had no effect on the stored procedure working, but when I copied the query to QA it got the error below, so I removed them all and added them in one line at a time to find the problem.
--Server: Msg 170, Level 15, State 1, Line 3
--Line 3: Incorrect syntax near ','.
Below are the 2 querys, the only difference is the Char(10) between Amt6 and Amt7!
http://www.rakbiz.com/download/broken.txt
http://www.rakbiz.com/download/working.txt
I also tried char(13) with same results.
any ideas why this is happening or how to add line breaks for readability without this problem?
Thanks for your help!
I am trying to get all the records where a particular field ( description) has no more that two characters
something like this
select * from
mytable
where description.lenght < 3
I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.
I am not using CAST in the design grid. Is this even possible?
I am modifying the underlying SQL code. Here is the line that is giving me trouble:
CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequence”
Hi,
alter PROCEDURE [dbo].[PPUpdateIWDetails]
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate datetime
)
AS
BEGIN
By default iam passing 12 char itemid as parameter...
Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN
DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END
Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same)
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN
DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END
Does anyone know why this statement would fail? I have created my own assembly which is on the server and when i run the mdx query call myassembly.mystoredproc() it returns data, but now when I use call myassembly.mystoredproc() it returns an error.
using an mdx query this works fine
call AsmTest.Asm.Analysis.TestClass.NameMe()
When using openquery i get an error
select * from openquery(NV, 'call AsmTest.Asm.Analysis.TestClass.NameMe()' )
OLE DB provider "MSOLAP.3" for linked server "NV" returned message "Prepare is not safe during execution of the NameMe stored procedure.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "call AsmTest.Asm.Analysis.TestClass.NameMe()" for execution against OLE DB provider "MSOLAP.3" for linked server "NV".
linked server definition
USE master
GO
/* Add new linked server */
EXEC sp_addlinkedserver
@server='NV', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='nvsifwfp', -- analysis server name (machine name)
@catalog='ARTSDW' -- default catalog/database
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
I create two tables:
create table table1
(
col1 char(1)
)
go
create table table2
(
col2 char(2)
)
go
I add some records to two tables after createing operation completed.
Then i use dbcc page command to oversee the structures of data page in two tables.
I found some interest things:
The rows in two tabes take up same space:9 bytes
You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE
or calculate from the offset array
Any suggestions?
I am having hard time figuring out what query I need to write.
What I want to do is, when users enter a word into a textbox, it displays results similar to it. Something similar to what Google does.
This is what I am doing now.
For example I will use "John Smith"
1. I get the first 3 letters of the string "Joh"
2. Get the last letter of the string "h"
3. and this is what I search for "Joh%h%"
This works, but I still have some problems. I want to be able to search like Google, where it recognizes words. Does anybody know how I can do this, or send me a link with an example?
Thank you
I would like to write query which can1. ignore whether the search keyword is Upper case or lower Case2. deal with tense or verb form related problem eg. comparing --> also search compareAny Idea
View 9 Replies View Relatedhi all
I have the follwoing search page:
several text boxes in case they want to search for SSN, first name,
last name, zipcode, several dorpdown lists, and a set of radio buttons.
I want to create the SQL statement to display the results
I am checking if any of the text boxes has anything in it, if yes add
"where something = value" to the query, if not then skip... now let's
say it goes through textbox 1 and finds it empty, so it jumps to the
second one it find info in it, how do I deal with the "and" clause
within the query?
any tips are fine.. (im using c#)
thanks
I am writing a small search feature to return a list of companies whose name "Begins with" a certain string (up to 5 chars) provided by the user via a textbox. I want the results to only return results that begin with the letter/letters specified. Below I will put the code that I came up with that isn't working quite how I expected. I am new to this so any assistance and short explanation would be very much apperciated.
sql="SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 "
sql=sql & "FROM cmmst cm "
sql=sql & "WHERE cm_cname1 + ' ' + cm_cname2 LIKE '%" companyNameBegins,"'","''") & "%' " sql=sql & "AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') " sql=sql & "ORDER BY cm_s16 DESC, cm_cname1 ASC"
companyNameBegins is the string passed in by the user
Thanks,
Zoop
Hello-
I am having a difficult time figuring out the differences between the following 2 queries. The first query ( which should return around 8000 records) when run against a Sql7 DB with 70000 records will always timeout (timeout set at 30 secs), but the 2nd query will return the correct amount of records ( about 8000 ) in under 8 secs.
Could someone shed some light on this for me?
Thank you
Robert
******Start Query 1******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From Auctions A Where (
((title LIKE '%STUB%' )) or ((description LIKE '%STUB%' ))
) And (Status In ('A', 'P') Or (Status In ('B', 'C')
And EndDate >= '02/25/2001')
) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
Order By Lot Asc
*****End Query 1*****
******Start Query 2******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From
(Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate,Description From Auctions A Where
(A.Status In ('A', 'P')
Or (A.Status In ('B', 'C') And
A.EndDate >= '02/25/2001')
) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
) as X
Where (
( (title LIKE '%STUB%' ))
or ( (description LIKE '%STUB%' ))
)
Order By Lot Asc
******End Query 2******
Hello All!
I'm building a serch page on website. It basicly searches 2 tables. Right now, the way I have it so the user can enter a value in one of the fields, and not the others. And it will return the value. For example, put in '34' in the age field, it will return all people with 34. Now if you add 'Jones' to the last name field, it will return all people with the age of 34, and all people with Jones.
But what if I want it so it returns only the people with the age 34, and Jones. But leave the other fields blank. The later than add 'Jim' to the first name, so now it will return 34, jones, jim. I tried to use the AND operator, but then it requires that field to be filled. Here is my code.
ALTER PROCEDURE [dbo].[SrchActiveII]
@FName nvarchar(50)=NULL,
@LName nvarchar(50)=NULL,
@DOB nvarchar(50)=NULL,
@Acct nvarchar(50)=NULL,
@Login nvarchar(50)=NULL,
@Status nvarchar(50)=NULL,
@Rmark nvarchar(255)=NULL,
@Room nvarchar(50)=NULL,
@Age nvarchar(50)=NULL,
@Type nvarchar(50)=NULL,
@Misc bit = NULL
AS
BEGIN
SELECT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks,
Locations.Loct_Desc, Active_Orders.Rm_Desc, Active_Orders.Age, Active_Orders.Type, Active_Orders.Stat,
Active_Orders.Order_ID, Active_Orders.Login, Active_Orders.Misc
FROM Active_Orders INNER JOIN
Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN
Locations ON Active_Orders.Location_ID = Locations.Location_ID
WHERE (Active_Orders.First_Name =@FName) OR (First_Name IS NULL) OR
(Active_Orders.Last_Name =@Lname) OR (Last_Name IS NULL) OR
(Active_Orders.DOB = @DOB) OR (DOB IS NULL) OR
(Active_Orders.Account_Number = @Acct) OR (Account_Number IS NULL) OR
(Order_Status.Status = @Status) OR (Status IS NULL) OR
(Active_Orders.Remarks = @Rmark) OR (Remarks IS NULL) OR
(Active_Orders.Rm_Desc = @Room) OR (Rm_Desc IS NULL) OR
(Active_Orders.Age = @Age) OR (Age IS NULL) OR
(Active_Orders.Type = @Type) OR ([Type] IS NULL) OR
(Active_Orders.Login = @Login) OR ([Login] IS NULL) OR
(Active_Orders.Misc = @Misc) OR (Misc IS NULL)
END
Thanks!
Rudy
Hi,
I am making a search query for jobs site. I have a situation that if user type following string in my search box:
Web Developer Designer ISF New York
It should display all jobs conatins any one of given words. following are columns in my jobs table: (Query will be applied on Location, Title, Description, Company fields)
Jobs
JobID
PostedDate
Company
URL
Location
Title
Description
Contact
please help me what will be the query.
Thanks,
Bye.
Hi,
I'm writing
small search engine for my page. I need SQL query that could do this:
Source:
tblColours
------------
Red
GreenRed
White Red
Yellow
Blue Green
Yellow RedF
Search
string: Red
Required
results:
Red
White Red
Yellow RedF
As you can
see I need all occurrences of word Red and word Red* but I don’t need *Red or
*Red* so I can't use LIKE %Red% :(.
P.S. Sorry
for my English.
Fizikas.
i have two tables,
Opportunity
[OpporID] [numeric](18, 0) IDENTITY (1000, 1) NOT NULL ,
[OpportunityID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OpportunityTypeID] [numeric](10, 0) NOT NULL ,
[SLABased] [int] NOT NULL ,
[LoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[AccountID] [int] NOT NULL ,
[GeographyID] [int] NOT NULL ,
[VerticalID] [int] NOT NULL ,
[BDMID] [int] NOT NULL ,
[Probability] [int] NOT NULL ,
[PASStatus] [int] NULL ,
[InsertedDate] [datetime] NULL ,
[InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedDate] [datetime] NULL ,
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedFlag] [int] NULL
and SKILL
[SkillNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[OpportunityID] [numeric](18, 0) NOT NULL ,
[OrderId] [numeric](18, 0) NOT NULL ,
[PracticeID] [int] NULL ,
[SkillID] [int] NOT NULL ,
[NoOfPeople] [int] NOT NULL ,
[Clientinterview] [int] NOT NULL ,
[Location] [int] NOT NULL ,
[JDAttached] [int] NOT NULL ,
[JDFilePath] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [int] NULL ,
[Experience] [int] NULL ,
[InsertedDate] [datetime] NULL ,
[InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdatedDate] [datetime] NULL ,
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedFlag] [int] NULL ,
[GeoLocation] [int] NULL
)
i want to make a stored procedure for custom search on these two tables
with the following fields given to the user as an option to make his
choice..
from opportunity table -
OpportunityTypeID,SLABased,AccountID ,
GeographyID,
VerticalID,
BDMID,
Probability
and from skill table
SkillID, Location, GeoLocation
and return all the fields of opportunity table.
Can some make the stored procedure for me..
thanks a lot.
Hi!In short description I want to make table with Articles and search Query for this table like there is search engine at templatemonster.com with templates and categories. Basicly I have table:- id - cat_name- 1 - Software- 2 - Hardware- 3 - Games- 4 - Internet- 5 - Events etc.And for example Article is in Software, Games and Events category, "1,3,5".Now user select to show articles in Games and Events categories, so "3,4,5"How write this search query? I don't have idea.
View 2 Replies View RelatedHi,
I'm wondering about the following:
I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I havereviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're foundinside some DLL.
I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere.
So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)?
Thanks in advance for your advise.
Stewart
Hi everyone,
I'm trying to implement SQL Server database search. The details are:-1. I have table called EMPLOYEE has FNAME,LNAME etc cols.2. User might look for any employee using either FNAME or LNAME3. I have search box in asp.net where user could enter search stringThe sample data:FNAME LNAMEabc georgedef georgerkis litarose lita
The query i wrote:SELECT * FROM EMPLOYEE WHERE lname like '%' + searchArg + '%'My problem is:-1. let's say user is looking for employee "george"; In search string instead of typing actual word "george", user could type "jeorge"; because the name pronounce or sounds like similar.Same thing with user could type "leta" instead of "lita". Again these are all similar sounds.
When you look for "jeorge" in GOOGLE; it says "did you mean george"; i would like implement something like that. somewhere i saw SOUNDEX would do what i am looking for; but i no luck for me.
Is this possible anyway in T-SQL or Fulltext search.
Your help is greatly appreciated.
ThanksBob
i have an sp that does not use full text searching in SQL 2000 i was wondering if someone could point me in the right direction of changing/upgrading this to a full text search? would you recommend upgrading the search sp? the following is the sp i have(it uses a function to count the number of words):CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(100), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(100), Rank INT, ImageALT VARCHAR(100), ArtistName VARCHAR(50), Stock INT, SearchCoverQuality INT, SearchAlbumQuality INT)
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, ArtistName, Stock, SearchCoverQuality, SearchAlbumQuality, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, Product.Stock, AlbumSingleDetails.CoverQualityID, AlbumSingleDetails.QualityID, 3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+dbo.WordCount(@Word1, ArtistName)+ 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+dbo.WordCount(@Word2, ArtistName)+ 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+dbo.WordCount(@Word3, ArtistName)+ 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+dbo.WordCount(@Word4, ArtistName)+ 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)+dbo.WordCount(@Word5, ArtistName) AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, ArtistName, Stock, SearchCoverQuality, SearchAlbumQuality, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, Product.Stock, AlbumSingleDetails.CoverQualityID, AlbumSingleDetails.QualityID, (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+dbo.WordCount(@Word1, ArtistName)) * CASE WHEN @Word2 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+dbo.WordCount(@Word2, ArtistName) END * CASE WHEN @Word3 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+dbo.WordCount(@Word3, ArtistName) END * CASE WHEN @Word4 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+dbo.WordCount(@Word4, ArtistName) END * CASE WHEN @Word5 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)+dbo.WordCount(@Word5, ArtistName) END AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, ArtistName, Stock, Rank, CASE SearchCoverQuality WHEN 1 THEN '5stars.gif' WHEN 2 THEN '4stars.gif' WHEN 3 THEN '3stars.gif' WHEN 4 THEN '2stars.gif' ELSE '1stars.gif' END AS CoverQuality, CASE SearchAlbumQuality WHEN 1 THEN '5stars.gif' WHEN 2 THEN '4stars.gif' WHEN 3 THEN '3stars.gif' WHEN 4 THEN '2stars.gif' ELSE '1stars.gif' END AS AlbumQualityFROM #SearchedProductsWHERE Rank > 0 AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1 AND @PageNumber * @ProductsOnPageORDER BY Rank DESCi was wondering if i can use full text search on many tables at once?thanks in advance!Tuppers
Hi friends,
Can u let me know how the sql-query, that displays only the first 20 records,and while clicking next button the subsequent records should be diplayed in a web appliaction(search). i want to implement through ADO objects.Because if the search query results in hundreds of records and everything is taken into the memory it will badly affect the performance of the system !right?
Regards
rajesh
Hi,
I'm trying to do a query to do a search by keywords. For example if the user searches for "Hottest restaurants in Atlanta" i want to write a query that searches that exact phrase first in a database field called keywords and if it shows up there then that record should be displayed first.. and then after do a search for EACH one of the words in a number of fields in the database and see if it shows up.
here is what i have so far but it always returns the same thing. any ideas?
$searchArray=explode(" ",$search);
$query = "SELECT ID,name,description from restaurants WHERE keywords = '$search' OR (";
foreach($searchArray as $key){
$query = $query . " (name LIKE '%$key%' OR subType LIKE '%$key%' OR features LIKE '%$key%' OR neighborhood LIKE '%$key%' OR region LIKE '%$key%') OR";
}
$query = substr($query,0,strlen($query)-3);
$query = $query . ") order by keywords";
any ideas?
I am not very familiar with the syntax of MS SQL and I am trying to write a stored procedure which would do a search and return matching records.
This is what I need to achieve:
for instance I create a form with 4 text fields
- First Name
- Last Name
- Employee ID
- Date
I am interested in writing a stored procedure that would run a select query based on the input in the text fields
e.g.
- if the user enters First Name and the Last Name (leaving Employee ID and Date fields blank) query should be something like
select * from Employee where FirstName like @FirstName and LastName like @LastName
- or if the user enters only the Employee ID stored procedure should run a query similar to
select * from Employee where EmployeeID like @EmployeeID
I have 2 tables.
Clients
Contacts (multiple contacts for one client)
I'm trying to do a search that pulls a contacts where the search matches either the First Name, last Name or the clients name. If it does return clients, I'd like it to also return all the contacts associated with it.
I have two problems:
1. The query is not bringing up a lot of clients. In many cases a letter brings nothing back. Like G and H even though A and B return results.
2. If it finds a client it only returns one contact. I'd like it to return all contacts for the client.
Here's my query:
SELECT addressbook.clientid, clients.clientname, addressbook.addressid, addressbook.fname, addressbook.lname FROM clients, addressbook where clients.clientid = addressbook.addressid
AND (addressbook.fname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR addressbook.lname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR clients.clientname LIKE
'" . strtoupper($_GET['txtsearch'])."%')
Hello everyone,
Obviously, I'm new here so I'd like to start by thanking anyone for their help with this very newbie SQL search query question.
I have been tasked with trying to figure out how to get search results from several different columns of two different tables within a database. I have successfully accomplished this, but one column has many many duplicates. I have read on these forums about using the DISTINCT command to eliminate this, but so far have been unsuccessful. Here is the search I'm using...(columns 1, 2, and 3 are from one table and columns 4 and 5 are from another. The results need to be in ascending order (alphabetical) of column1.
select column1, column2, column3, column4, column5 from table1, table2 order by column1;
The result of this is great except that column1 has many duplicates.
I tried entering the search like this...
select distinct column1, column2, column3, column4, column5 from table1, table2 order by column1;
...but that didn't seem to help.
Can someone steer me in the right direction?
Thanks for any and all assistance.