Just wanted to run this idea past u all before I have a go at it.
I have two tables A & B that are similar to the below
Table A
Name1 Name2 Name3
Tom Bill John
Gary Harry Eric
TableB
Name1 Name2 Name3
John Bill Tom
Tom Eric john
Leslie Philip Colin
What I wanted to do is see if the the records from tableA row 1 exist in tableB
As you can see they can appear in any order, partially or not at all.
What I propose to do is take tableA Name1 and see if it matches tableb row1 name 1 OR name 2 OR name 3 and if I find a match use a variable to assign the the value 1 (so I can then see if the match is full (score three) partial socre 1 and two or not at all (0)
Then I will need do the same for tableA row 2
Then goto row 2 of table a and start again.
Does this make sense? Are there beter ways of doing this.
What I need to do is identify records from Table 2 that do not have an Activity in the Act_Listing field contained in Table 1. So for Part_ID=1927, code 33 exists in Table 1, but code 14 doesn't, so that record should be selected.
Hi there,i am having some problem related to SQL server........ Actually i am having a table called ZipCodes that have around 80,000 rows... and the size of the table is around 100 MB...... and my table is now on web Server,. now my problem is that when i fire some query that needs to go through whole of the table then it estimated time to execute the query comes to be 13 seconds and the corsor threshold is set to 7 seconds (and i can't change that)....... so the SQL server cancels the query to be fired........Now i need some Methodology/Technique through which i can search Large Tables with minimum calculations in minimum Time............(Any Ideas)....
i have multiple tables that vary very little in name. what i mean by this is they are named tbleffort1, tbleffort2 etc. i need to search all the tables together. there is a large constantly changing number of tables, so i would prefer not to have to write them out one by one! any suggestions would be most appreciated!
Basically, I want to be able to have a stored procedure that will search through all the stored procedures looking for a given string and returning the names of all the stored procs that contain that string value.
I know I can script it off and then do a text search in Notepad or whatever, but I figured there must be a more elegant way to do it. More than likely dealing with the DB sys tables.
Is there already a tool in SQL Server that does this? Or has anybody had a chance to roll their own?
Relative SQL newbie here......this is probably easy, but....Lets say I have a table (MainTable) that stores a list of input table names,a primary key (PKey), and a field called "Configured" for each one. Each ofthese input tables also contain a field called "Configured", which is set totrue or false in another process based on an OrderNumber. (So an order'sinputs are stored in several input tables, and the MainTable is a summarytable that shows which input tables have been configured for any givenOrderNumber).What I need to do is open each input table, and look for a record containinga specific OrderNumber and where Configured=true. If a record is found, Ineed to update the Configured field for that table in the MainTable, andthen move on to the next sub-table.The way I'm doing it now is with simple SQL and loops. Here is the basiccode (ASP):*****************************************OrderNumber = "562613" ' the current order that is being processed' reset all configured flagssql = "UPDATE MainTable SET Configured = 0"conn.execute sql, , &H00000080' get list of all tablenamessql = "SELECT InputTableName, PKey FROM MainTable WHERE InputTableName <>'---'"set rsTableNames = conn.execute(sql)while not rsTableNames.eof' test each input table for configured flagsql = "SELECT Configured FROM " & rsTableNames("InputTableName")& _" WHERE Configured = 1 AND OrderNumber = '" & OrderNumber &"'"set rs = conn.execute(sql)If Not rs.EOF Then' update the main tablesql = "UPDATE MainTable SET Configured = 1 WHERE PKey='" &rsTableNames("PrimaryKey") & "'"conn.execute sql, , &H00000080end ifset rs = nothingrsTableNames.movenextwend*****************************************There has to be a faster way.. I think.... maybe something that could bewritten as a stored procedure? I use a similar technique in a couple ofother places and it's a bit of a performance hit, especially as the numberof input tables grows.TIA!Calan
I am trying to find all the email addresses with a " ._" I use '%._%' but it returns all records. What is the correct syntax? Also, is there a way to search for a field where the underscore is followed by a single alpha letter and then another underscore? like bla_A_bla or bla_Z_bla.thanksMilton SELECT DISTINCT fname, lname, inet FROM ocadbo.notes where inet like '%._%'
the application registers the student details to a course. each student gets a new registration no during registration.
the app should identify repeaters to a particular course by checking another table RegHistory, which stores the details of student registrations for the previous 5 years.
to determine whether a student is a repeater or not, we have to search for an exact match in RegHistory table (where the student name, guardian name and date of birth in both tables match with the corresponding entries in Registration table).
here is my question,
if there are 100,000 students registering in each academic year, we will have 500,000 records in RegHistory Table and 100,000 records in studReg table
if i start searching for a repeater, i guess i will have to loop through all records in studReg, for an exact match in RegHistory, which wil be a time consuming process.
is there any other options to search for repeaters ?
hi all, I need help in selecting records from a table based on the given search criteria. spec: select * from table where col1='x' and col2='y'... and col6='q' i may give any combination of column values. I mean I can't provide 6 values in 'where' condition all the time i submit query. help me with a stored proc which has 6 input parameters for the 6 columns.
I am searching for an add-in to ssms which lets me choose which server to deploy my code.
For example, I have a complete statement with CREATE PROCEDURE and all code in my query window.
Now I want to run/deploy this code to many servers at once. Instead of having to reconnect to all server (5), I want an add-in which have checkboxes for me to select.
Hi,I have come to point in my db design where I'm trying to figure whichis the best approach in making it generic (does this really matter?!?)Senario - I have a table called JOBS and this table contains fieldsuch as JobTitle, JobDescription, Salary etcI want to add to this table other attributes which are specific to acertain Job Industries.Solution - Add a join table for each type of industry containingattributes (db is now not generic) OR add a new table with a IndustryType field and a XML field containing the industry specificattributes.If I go the XML way will this just make it complex and slow to query?If not, what is the best way to query an XML field?Thanks,Jack
I am having a problem with seaching my tables. On my asp.net page ihave 3 text boxes. One for an ID and NAME and ADDRESS. I want to beable to search a table by using all or any combination of the 3 tosearch. But i cant get it right. i am using c# and sql server.any help ideas would be a great helpthanks in advanceructions
i'm making a web page for a clinic.it needs to be able to search for patients by first name, surname, date of birth and patient number.i'm using visual web developer and i have my database, my data source and GridView grid.i want 4 text boxes for my first name, surname etc. when u click enter on any of them i want to retrieve all their data and display it in the gridview.at the moment i have one text box on the web page and through the "Configure data source" option on the grid view i can retrieve the specified data but for only this one item, e.g. SELECT * FROM [Patients] WHERE ([DOB] = @DOB). if i add another text box to my web page, and don't do anything to it, the query wont run. if i add and "AND" statement to the query, e.g. SELECT * FROM [Patients] WHERE (([DOB] = @DOB) AND ([FirstName] = @FirstName)), again it won'r run or return and data. any ideas on what i can do or where i'm going wrong. thanks
I have a table GO CREATE TABLE [dbo].[Speech] ( [SpeechId] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkSpeech_SpeechId PRIMARY KEY, [UniqueName] [varchar](52) NOT NULL, [NativeName] [nvarchar](52) NOT NULL, [Place] [nvarchar](52) NOT NULL, [Type] [smallint] NOT NULL, [LanguageId] [char](2) NOT NULL CONSTRAINT FkSpeech_LanguageId FOREIGN KEY (LanguageId) REFERENCES Language(LanguageId) ON UPDATE CASCADE ON DELETE CASCADE, [SpeakerId] [int] NOT NULL CONSTRAINT FkSpeech_SpeakerId FOREIGN KEY (SpeakerId) REFERENCES Speaker(SpeakerId) ON DELETE CASCADE, [IsFavorite] [bit] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDate] [datetime] NULL ) Now I want to search the Table Speech Sometimes by : SpeechIdSometimes by : SpeakerIdSometimes by : LanguageIdSometimes by : SpeechId And LanguageIdSometimes by : SpeakerId And LanguageId All can have conditions with IsVisible, IsFavorite and Type columns. for example I need all Speeches withany particular SpeakerId and LanguageIdwith IsVisible equals to trueand IsFvaorite No Matterand Type equals to Audio For these type of queries I think the solution is GO CREATE PROCEDURE [dbo].[sprocGetSpeech] @speechId int = NULL, @uniqueName varchar(52) = NULL, @nativeName nvarchar(52) = NULL, @place nvarchar(52) = NULL, @type smallint = NULL, @languageId char(2) = NULL, @speakerId int = NULL, @isFavorite bit = NULL, @isVisible bit = NULL AS SELECT SpeechId, UniqueName, NativeName, Place, Type, LanguageId, SpeakerId, IsFavorite, IsVisible, CreatedDate, ModifiedDate FROM Speech WHERE SpeechId = @speechId AND UniqueName = CASE WHEN @uniqueName IS NULL THEN [UniqueName] ELSE @uniqueName END AND NativeName = CASE WHEN @nativeName IS NULL THEN [NativeName] ELSE @NativeName END AND Place = CASE WHEN @place IS NULL THEN [Place] ELSE @place END AND Type = CASE WHEN @type IS NULL THEN [Type] ELSE @type END AND LanguageId = CASE WHEN @languageId IS NULL THEN [LanguageId] ELSE @languageId END AND SpeakerId = CASE WHEN @speakerId IS NULL THEN [SpeakerId] ELSE @speakerId END AND IsFavorite = CASE WHEN @isFavorite IS NULL THEN [IsFavorite] ELSE @isFavorite END AND IsVisible = CASE WHEN @isVisible IS NULL THEN [IsVisible] ELSE @isVisible END Can anyone tell me? Is it right way to do?Do you have any better solution?If my solution is better then Is there any performance loss with that query?
hello all..i have make a searching, but is not good. my code like that:Public Class getall Public Function getitem(ByVal id As String) As DataSet Dim con As SqlConnection = New SqlConnection("Data Source=BOYsqlexpress;Initial Catalog=GAMES;User ID=ha;Password=a") Dim ds As New DataSet() Dim adapter As New SqlDataAdapter("select * from [item] where name like '%" & id & "%'", con) Try con.Open() adapter.Fill(ds, "user") Return ds Catch ex As Exception Console.Write(ex.Message) Finally con.Close() con = Nothing End Try ' Next Return ds End Functionand class my item in database is containning dragon ball 3, counter strikeif i insert dragon, it can display dragon ball 3.but if i insert dragon 3, it not display dragon ball 3.it should display dragon ball 3 .how should i change my code?thx...
hello everyone i need C# code in wh we sent "UserID" to table named "Users" and if such "UserID" exists in "User" table we get his name from the table else we got message that no such record exists. i have tried to write that code ,it works only if the value exists . in case of no value an exception is thrown. please do sent me that c# code. thanks for your consideration.
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
I have a products table with product attributes in a second table, together they describe a full product. I have a product title, a list of providers, description text, and keywords. I would like to do a search across these fields, and so far my research has shown that the Full Text Search component of SQL Server is the way to go. However, I am not sure this will be possible based on what is installed on the hosted server, so I am wondering if there is a unique, cool way of doing this without Full Text Search?
i currently have a function and a storedpro in my sql database they are: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(50), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(50), Rank INT, ImageALT VARCHAR(100), Artist VARCHAR(50)) /* Populate #SearchedProducts for an any-words search */IF @AllWords = 0 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, 3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+ 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+ 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+ 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+ 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) 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, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)) * CASE WHEN @Word2 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description) END * CASE WHEN @Word3 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description) END * CASE WHEN @Word4 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description) END * CASE WHEN @Word5 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) 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, Artist, RankFROM #SearchedProductsWHERE Rank > 0 AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1 AND @PageNumber * @ProductsOnPageORDER BY Rank DESCand:CREATE FUNCTION dbo.WordCount(@Word VARCHAR(20),@Phrase VARCHAR(1000))RETURNS SMALLINTASBEGIN /* If @Word or @Phrase is NULL the function returns 0 */IF @Word IS NULL OR @Phrase IS NULL RETURN 0 /* Calculate and store the SOUNDEX value of the word */DECLARE @SoundexWord CHAR(4)SELECT @SoundexWord = SOUNDEX(@Word) /* Eliminate bogus characters from phrase */SELECT @Phrase = REPLACE(@Phrase, ',', ' ')SELECT @Phrase = REPLACE(@Phrase, '.', ' ')SELECT @Phrase = REPLACE(@Phrase, '!', ' ')SELECT @Phrase = REPLACE(@Phrase, '?', ' ')SELECT @Phrase = REPLACE(@Phrase, ';', ' ')SELECT @Phrase = REPLACE(@Phrase, '-', ' ') /* Necesdbory because LEN doesn't calculate trailing spaces */SELECT @Phrase = RTRIM(@Phrase) /* Check every word in the phrase */DECLARE @NextSpacePos SMALLINTDECLARE @ExtractedWord VARCHAR(20)DECLARE @Matches SMALLINT SELECT @Matches = 0 WHILE LEN(@Phrase)>0 BEGIN SELECT @NextSpacePos = CHARINDEX(' ', @Phrase) IF @NextSpacePos = 0 BEGIN SELECT @ExtractedWord = @Phrase SELECT @Phrase='' END ELSE BEGIN SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1) SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos) END IF @SoundexWord = SOUNDEX(@ExtractedWord) SELECT @Matches = @Matches + 1 END /* Return the number of occurences of @Word in @Phrase */RETURN @MatchesENDmy database has many table but product is linkinked to albumsingledetails with productid in the albumsingledetails table, the the albumsingledetails table has the artistid in it which links to the artist table. I have tried searching for an artist but it does not find them!! can anyone see where i have gone wrong?
I want to search all of the employeeid #'s in the employee table for any mis-typed information. so if they put a . in the string i want to return that employee's info. as well as any letter a - z. I need to do this in sql and return it to a dataset for my crystal report. any ideas?
Hello,How do I search through the gridview? Would I do this at the sqldatasourcelevel?I figured that I sould search with the datatable.select but how do I accessthe datatable of the sqldatasource?I am using ASP.NET 2.0 with VB.Thanks for your helpJ
I need to search all user written SPs which have particular text in them. One way to do it is to open each SP in some notepad or word processor and search for the particular text.Is there any efficient way to do it ??
I'm quite new to SQL, just wondering is there a way to search the entire database (instead of a table using select) for something? Say I create a new database to keep track of my CDs. A new table is created in this database for each CD, and of course each row of this table represents one track. Is there a way, either using tsql or through the enterprise manager or query analyzer, to say list all songs that are sung by a singer with the first name "John"?
I've tried the object finder in query analyzer, doesn't quite work. I'm looking for something like
select * from * where singerfirstname = 'John'
This of course doesn't work... please help! Thanks so much in advance!
I'm trying to find out what modifies a certain table (yay for more debugging without documentation). Using some TSQL provided by a friend, I've searched through the stored procedures, and I've searched through the various pages in the web app, but the only references to the table are updates and selects (not inserts).
The table itself is intended as an archive for another table.
Unfortunatly, the documention for this web app is worthless, and the code has little to no comments to boot.
Is there any way to search through DTS packages aside from opening them each individually and looking at the parts?
I want to create a stored procedure which takes a project name and returns any project with a name similar or = to the input parameter. This is so the user can search for a project although the spelling may be incorrect or whatever.....
Any hints on how to start this? It seems that I could use SOUNDEX or DIFFERENCE, but I don't know where to start.
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]%'
