I have two table, both table have a col which stored the data in CSV format, ie "ab , bc, de", etc
I would like to select the item where "any one item" in first table is same as "any one item" on second table.
I have alread have the private CSV function which can convert the string and return as table. However, I find i cannot do it like this select ..... where dbo.CSVfunction(tableA.colA)
Thx//CSV functiondeclare @separator char(1) set @separator = ','
declare @separator_position int declare @array_value varchar(1000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) select @array_value = RTrim(@array_value) select @array_value = LTrim(@array_value) Insert @StringTable Values (Cast(@array_value as varchar(50)))
select @array = stuff(@array, 1, @separator_position, '') endre @separator char(1) set @separator = ','
declare @separator_position int declare @array_value varchar(1000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) select @array_value = RTrim(@array_value) select @array_value = LTrim(@array_value) Insert @StringTable Values (Cast(@array_value as varchar(50)))
select @array = stuff(@array, 1, @separator_position, '') end
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
Okay I've posted something like this in another area but it doesn't seem to be focused towards the right people. I need ya'll SQL experts! I have a database table that has 5 columns: Time Status Message Source IP The Status column has only two choices "up" or "down" I'm looking for some kind of advanced query that will do the following: I need the query in my application to pull the table and then match up IP, Status, and Message columns. It should only find One match and then once it has found a match it needs to recognize that one is an "up" status and one is a "down" status and then remove them from the database. Does that make sense? Is it impossible?
Hi, This is a where clause I am using in a search. WHERE (ADDRESS_STREET LIKE '%' + @Search + '%' ) I am trying to do a search which returns the most matching record. For example if I have a record with Denver as text . If I search for Denvr the spell error is intended , I will not get the result. How can I create a stored procedure to counter probable spelling errors and return matching results in a ranked order. Thanks
say I have a list from an sql statement (results list) this list contains 10 items
In another table, in one particular column - there is a match for one of these items from the initial list.
SO... this may be the list _____________________ itemnumber 1 2 3 4 5 6 7 8 9 10 ------------------------------
in the other table there is a match... but just for one item on that list. ____________________ othertablefield 11 13 14 3 <------ match 99 78 ----------------------------
My company is going to start a Cancel web site so customers can cancel their future orders by simply filling out a form. I was wondering what would be the best way to compare the cancel db to db that is used to store customer information and order information.
The cancel website will only hold the name, address, and Credit Card Number used. There will not be a customer/order number. The simple Credit Card to Credit Card search will be easy but I was more worried about comparing the rest of the information in case the Credit Card search fail. I was told the "standard" look up was to take the first 3 letters of the first name and the last 3 letters of the last name. If that comes up with more then one record, take that set and see if the zip code matches up and/or part of the street address matches up (counting we do not have very reliable people inputting the information into the system is not going to help this search).
Anyway, any suggestions on comparing the data would be great.
Through out the table any number of the columns can be null.
I wish to remove rows from this table where the columns values are contained in another row i.e Row 2 above is contained within row 3. Similarly, row 3 is contained within row 4. So, the only rows I want from the sample data above are rows 1, 4 and 5.
I hope I have explained my query adequately, and any help would be great appreciated. (Before I go mad...!)
Hi everyone. I'm developing a web search engine using asp and SQL Server 2000. I need to return records that matches with a string entered by users. In example, suppose my database to have this structure and it's filled like this:
ID NAME KEYWORDS --- ------- ----------- AA025 NAME1 attached, atic, common, business, hotels AA026 NAME2 headache, medicin, aspirins, heat, health AA027 NAME3 at, services, music, electronics
suppose that user enters 'at'. By now, i'm using this pattern '%<input_text>%'. So in this example, pattern would be '%at%'. As i remarked in the fields above, the three fields matches, and that's not what i'm looking for. I want that the result of that query be just the last field, with 'AA027' ID. Thanks in advance guys. Cheers.
I'm getting the following error when running dbcc checkdb and no clue as to what to do.
Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1328111872,referenced_minor_id=5) of row (class=0,object_id=1795589535,column_id=0,referenced_major_id=1328111872,referenced_minor_id=5) in sys.sql_dependencies does not have a matching row (object_id=1328111872,column_id=5) in sys.columns.
What can be done with Sql Server for this problem?
In this query:
select A, B, C from (select A, B, C from table1) as G1, (select A, B, C from table2) as G2
where G1.A = G2.A and G1.B = G2.B and G1.C = G2.C
a record in table1 can be matched with more than one record in table2, and I don't have any other field in the tables that helps me link one G1 record to a specific G2 record. What can I do to match each G1 record to only one (if any) G2 record (if there are two records in table2 which are identical, I want the query to choose one of them and leave the other one unmatched)? Another question: is there a way to count records and attribute them a unique number? What is an index?
hi allI want to write a sotred procedure which has a parameter,called@name.The SQL statement must return all rows where this parameter is asubatring of a special column called FirstName.This procdeure do thesame ,but return all rows where @name is the exact matching.whatshould I do?/*This procedure return the exact matching*/CREATE PROCEDURE substring@name varchar(50)ASSELECT *FROM table1WHERE (table1.firstName LIKE @name);
When I write the above query, I want to exclude the AddIDs that have a Name_Full of DAVIELT. In the Name_Full column the only difference between the two strings is the "T" in DAVIELT.
Please note that it has to use a LIKE statement. The code is already written that way and I do not have control over it.
We are in initial phase of designing an application whose primary purpose is to match a list of loan defaulters with bank's customer database.
Let me make the picture more clearer to you,
There is a list of defaulters which contains about 1500000 names (Primary names and Aliases e.t.c), related to a person.
On the other hand there is database which contains bank's customer's (about 20, 00000 records) data.
We will have to run batch a scan for name matching against defaulters list to the current customer base of bank to check if any of existing customer is in defaulter's list.
This whole exercise of name matching should be finished in 4 to 5 hours (as required by user/client).
How can I optimize my data base design or processing logic to achieve this goal,
Should I use full text query or I should load data related to names in memory than carry out some logic for name match. These are two my primary thoughts.
I have 2 tables called Customer & CustomerArchive. Both Tables have the same Column names and Datatypes etc.
CustomerID Address Postcode
1 10 Alcove Street 4000
50 21 Spring Street 4032
33 5 Terrace Place 4010
What I need to do is compare each column except for the CustomerID to see if it is the same!
In other words, I need to compare Address in the Customer Table with Address in the CustomerArchive Table.
I have to do this with all columns (12) of them between the 2 Tables.
Is there a efficient & smart way to do this aprt from the obvious way of joing the 2 Tables via the CustomerID and performing a Customer.Address <> CustomerArchive.Address Customer.PostCode <> CustomerArchive.PostCode etc etc etc!
I'm working with Access 2,007, and I have an instruccion in SQL but when I execute it, it shows an error "" the instruccion and var in red
Dim Mes As String Dim CostoKw As Double Dim SQLUdate As String Mes = InputBox("Mes a Actualizar Costo: ") CostoKw = InputBox("Costo que se Aplicara: ") Select Case Mes Case 1 DoCmd.RunSQL "UPDATE Clientes SET Clientes.Costo01;" = CostoKw
The table's field Costo01 is the type Number and Double.
Init SC --- 89 Post NCOA --- 89 Post Supp --- 89 Revised Final State Counts --- 89 Revised Final State Counts --- 94 ***********************************************************************************************
Since "Revised Final State Counts" appears in both cycles 89 & 94. How can I query the table so that I only get that 1 record?
Hi folks I have a section in my networking site where people can write things about themselves, such as likes dislikes, hobbies etc for their profile. I am looking to provide a very basic profile matching service, and have done a bit of searching to find out what the best way to approach this feature is. I have looked at using the sql LIKE clause, but since the profile information can be any length, there seem to be too many variables to account for. I have also looked at identifying keywords in profile blurbs to do the matching too, but I am struggling to see how this could be done with relative ease. My reason for posting this is simply to get some experienced knowledge about where to start the problem, of even some resources to look at that I may not have found yet. If anyone could give me some advice on where to start i would be very grateful I am develping in vb, with an sql server 2005 back-end
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!
Hi, i am facing a scenario is that, iam matching table one table form oracle and another table form sql what is want is that using this two tables i want to display unmatched record from the orcale table very urgent help me please send the code for this scenario example in sql table i have fldinvoiceno 00000001 00000002 in oracle table i have fldinvoiceno 00000001 00000002 00000003 so matching the two table i need to get 00000003
Hey all, Got a little problem. have 2 matching tables on different servers with the EXACT same column layout and data (the tables are being replicated with MSSQL7) and one table is 200MB while the other is 2000MB. I'm running MSSQL7 SP2. Any ideas???
i have one task in which i have to match some attributes(required for creating a new databse) with the exiting database, are these attributes present in exisisting database, if yes how many , and how many are not,pls do reply
I've been trying to find and filter out data that can cause FOR XML to fail with the error:FOR XML could not serialize the data for node ?? because it contains a character (0x0006) which is not allowed in XML.I thought it would be simple, just identify the rows that don't match the XML spec URL...
However, the following doesn't work. select fieldname from tablename where fieldname like N'%[^'+nchar(0x9)+nchar(0xA)+nchar(0xD)+nchar(0x20)+N'-'+nchar(0xD7FF)+N']%'
Not only does this fail to find the offending rows, when I checked by inverted it by taking out the "^" it still returns no rows.When I reduced 0xD7FF down to 0xFF it returns data, but I cannot add the other range back in without all rows not matching.I've experimented with values and found 0x02E9 was the highest I could go without all values vanishing. Even then, numbers lower than that caused a large variation in the number of rows returned.My Field is NVarchar(100) in SQL_Latin1_General_CP1_CI_AS.
I'm currently using Oracle and receiving this error:
SQL Error: ORA-02291: integrity constraint (43437001.ATHLETE_FK1) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.
When inserting this value:
INSERT INTO ATHLETE VALUES ('A001','TOM','HANKS','03-FEB-90','USA','C001');
Basically without going into too much detail, our company gets databases arriving and put onto our systems which have been made my other organizations with no guarantee of what the primary key is or if this is one at all.
I should probably give my main problem in an example for clarity:
Currently I have a .csv file full of data that needs to be put into say TableA. However I do not know if TableA has a primary key or not, or if the file that needs to be inserted into TableA contains duplicate data. I have the importer sorted that does this if you ignore the problem of duplicate data, however what I would like is an MS SQL query that does the following (but I cannot figure it out):
Assuming we are reading through the file line-by-line and a check is performed each time:
1.If there is a line with a primary key in the file that matches a primary key in TableA in the database update that row in the database with the line in the file.
2. If there is no primary key on the table and there is an exact data match between the line in the file and a row in the database then update it.
3. If neither 1 or 2 are successful then just insert the data.
Obviously the potential lack of a PK here makes things a lot more convoluted.
I've recently tasked my self whereby I wish to rank the info off of a CV and then find matches for this information against a database containing job info, so I'm left with a list of jobs with their relevant perspective possible candidates. I'm thinking of importing the cv as text then manipulating it using Full_text ranking procedures.
Question is do people feel this is the best way of writing what is basically a matching process ?? Is there any 3rd party software out there which does this sort of thing already ??
I would like to match two sets of data. I have setup a view of data that contains a group of customers and their details. I want to view this data, but also find these customers in another table based on matching their surname and date of birth, then retreive the information stored on these customers from the second table.
Does anyone have any suggestions how i would go about doing this?
Thanks in advance Humate
quote:Originally posted by Michael Valentine Jones
It takes real skill to produce something good out of a giant mess.
Hi all,I have two tables:workgroups (wg_id, wg_name)workgroups_keywords (wgk_wg_id, wgk_keyword)Each workgroup has an associated list of one or more keywords.What I want do to at first was given a particular list of keywordsbring back a list of workgroups that have at least one matching keywordassociated with it.I have the following query:select distinct(wg_id), wg_namefrom workgroups, workgroups_keywordswherewgk_keyword in (#QuotedValueList(Keywords.wgk_keyword)#)andwg_id = wgk_wg_idorder by wg_nameThis works great.However, is there a way in a single query to order the returned rows bythe number of keywords that are found to be matching (in other words anorder by relevancy, the more keywords that match the more relevant thereturned row)?Thanks in advance.David
I apologise if this is a stupid question, but I would appreciated anyhelp on this subject.I want to create a view (VIEW_1 in example below) where I take numericvalues from a field in one table (DEPTH_FROM in TABLE_1) and find theclosest matching values from another field in another table (DEPTH_ATin TABLE_2) - the higher matching value and the lower matching value.TABLE_1BH_ID DEPTH_FROMB1 10.6B1 14.2B1 16.1B1 17.0TABLE_2BH_ID DEPTH_ATB1 9B1 10B1 11B1 12B1 13B1 14B1 15B1 16B1 17B1 18VIEW_1BH_ID DEPTH_FROM DEPTH_LOWER DEPTH_HIGHERB1 10.6 1011B1 14.2 1415B1 16.1 1617B1 17.0 1717Any assistance would be appreciated.Regards,Hennie
Hi all..I have two tables such as cisco and ciscocom. and i wan to compare eachrow of ciscocom with cisco having same column values. i wan to get thecount of matching columns for each row in cisco...eg:Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco hascolumns:fw,ports,sec,des,tput etc. i wan the number of matching columfor each row in ciscocom. please provide me with the procedure....Waiting for your response....
An application filters records based on names found in them. Forexample, looking through property buyer names, looking for banks andrelocation companies.I have a table of names and patterns:CREATE TABLE #Filters (Pattern varchar(100), IfWildCard int, Categoryint)INSERT #Filters SELECT 'Bank Of America', 0, 1INSERT #Filters SELECT '% Bank %', 1, 2INSERT #Filters SELECT 'Bank %', 1, 2INSERT #Filters SELECT '% Bank', 1, 2INSERT #Filters SELECT 'Credant Reloc%', 1, 3INSERT #Filters SELECT '%Relocation%, 1, 3The filtering matches the table of candidate names against the filters,and returns Category, where the where clause or join is(Candidate = Pattern AND IfWildCard = 0)OR (Candidate LIKE Pattern AND IfWildCard = 1)"Bank of America" matches an exact pattern, and also a wildcardpattern, and the two different matches give different values forCategory. Is there a way to control which match takes precedence, oris necessary to do it multiple times in the desired order, removingthose that hae already matched from consideration?Thanks,Jim Geissman
Hi,I'm just looking for some general advice on how to approach something.I have two tables A and B, containg common fields of product, salesdate and qtyo.Some records contain the same data and I want to dump them in a newtable called C and leave Tables A and B containg only data thatdoesn'r match.How do i go about approaching this??Regards,Ciarán
