String Matching
Nov 22, 2007
I'm on SQL Server 2005.
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!
View 2 Replies
ADVERTISEMENT
May 2, 2006
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
View 5 Replies
View Related
Oct 6, 2015
Consider the following: I have a table, say ORDERS, with these entries -
CustID
ProductID
1 CAN
2 2
3 1,2
4 4
5 1,2,3,4,5,CAN
6 10
7 CAN
8 1,CAN
I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.
View 12 Replies
View Related
Apr 3, 2015
Suppose I have string like
@strname varchar= = '3 April 15 abcd Oh rrrrrrrAAAAdd HJHJG'
and table contains two columns having rows like,
ID text
1 abcd ER
2 abcd AS
3 abcd Oh
4 xyz TR
5 azs WS
6 abcd O
7 OP trx
how can I search a ID's which are exist in my string.
result should be,
3 abcd Oh
6 abcd O
View 4 Replies
View Related
Apr 14, 2015
Suppose I have string like
DECLARE @strname varchar(50) = 'i_ncm_ai04';
DECLARE @SAMPLE_DATA TABLE
(
SD_ID INT NOT NULL
,SD_TEXT VARCHAR(10) NOT NULL
[Code] ......
how can I search a ID's which are exist in my string.
result should be,
3 i_ncm
8 i_ncm_a
View 2 Replies
View Related
Feb 4, 2007
Hi All
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.
View 5 Replies
View Related
Aug 18, 2006
Hi,
I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.
Wildcard
Meaning
%
Any string of zero or more characters.
_
Any single character.
[ ]
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).
Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.
In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:
WHERE
Gift_Date NOT LIKE "####*"
After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:
WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'
using single quotes and the % wildcard instead of Access' double quotes and * wildcard.
Just putting this out there for anybody else that is new to SQL, like me.
Regards,
Patrick Briggs,
Pasadena, CA
View 1 Replies
View Related
Jul 26, 2007
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?
View 1 Replies
View Related
Oct 5, 2007
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
View 2 Replies
View Related
Jan 15, 2004
HI,
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
----------------------------
How do I find that match with my sql statement?
View 1 Replies
View Related
Aug 2, 2005
Dear all,
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
View 1 Replies
View Related
Sep 3, 2003
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.
Thanks ahead of time
View 4 Replies
View Related
Sep 7, 2004
All
I have a table which contains 4 columns each of which are NULL or contain a 6 digit code.
Here is a sample of the table content:
COL1 COL2 COL3 COL4
----- ----- ----- -----
452359 NULL NULL 347406
NULL NULL 347406 347406
592319 NULL 347406 347406
592319 150009 347406 347406
592319 150010 347406 347406
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...!)
Regards,
Katherine
View 6 Replies
View Related
Mar 20, 2008
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.
View 14 Replies
View Related
May 22, 2007
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.
View 6 Replies
View Related
Feb 3, 2008
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?
Thanks in advance
Anna - Verona (Italy)
View 5 Replies
View Related
Jul 19, 2007
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);
View 2 Replies
View Related
Nov 12, 2007
I have a piece of code that uses LIKE matching to check for the existence of a set of characters in a string as follows
SELECT a.Name, a.Market
FROM Table a
Where a.Name LIKE 'DAVIEL%'
I have the following sample data in a look-up table
AddID Name_Full
100 DAVIEL
101 DAVIEL
102 DAVIELT
103 DAVIELT
104 DAVIELT
105 DAVIEL
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.
Any help will be much appreciated.
View 5 Replies
View Related
Feb 2, 2008
Hello friends,
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.
View 1 Replies
View Related
Feb 17, 2008
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.
Thanks
View 1 Replies
View Related
Jun 14, 2006
Hi,
I have the following table:
***********************************************************************************************
Sheet --- Cycle
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?
Thanks
View 1 Replies
View Related
May 5, 2007
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
View 2 Replies
View Related
Oct 3, 2007
A column in my database contains phrases such as "Extreme Golf: The Showdown" or "Welcome to Happy Land". I need to write a search engine so that users could type in phrases such as "Golf Extreme Showdown" or "Happy Land" and the correct, or closest matched results will be returned. I don't need variations of words, just phrase keyword match based search. I know I could do this by using multiple LIKE %% statements OR'd together, but this would be too performance intensive. So, I have heard I should use charindex somehow to achieve this in a stored procedeure. Does anyone have any clue how to solve this problem? Thanks!
View 7 Replies
View Related
Mar 21, 2008
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
View 4 Replies
View Related
Apr 17, 2001
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???
-Marc
View 1 Replies
View Related
Apr 17, 2008
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
View 3 Replies
View Related
Aug 22, 2014
This is my code, table and result,
declare @t_JobNoticeID table (cvid int, JobNoticeID int)
insert into @t_JobNoticeID values(2456, 24);
insert into @t_JobNoticeID values(4000, 124);
insert into @t_JobNoticeID values(245, 9);
insert into @t_JobNoticeID values(2456, 19);
insert into @t_JobNoticeID values(4000, 904);
insert into @t_JobNoticeID values(4000, 11);
insert into @t_JobNoticeID values(24, 19);
[Code] ....
My question is -
How update statement looks like compare RESULT A1 with x_JobMatching?
So, my FINAL RESULT shown as follow,
CVIDJobNoticeIDisMatch
1925450
6590690
459130
2456191
4569110
40009041
24560
900240
24191
View 5 Replies
View Related
Mar 30, 2015
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.
View 1 Replies
View Related
Sep 15, 2014
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');
With Tables:
CREATE TABLE ATHLETE(
ATHLETEID CHAR(4),
ATHLETEFIRSTNAME VARCHAR2(20),
ATHLETELASTNAME VARCHAR(20),
ATHLETEDOB DATE,
REPCOUNTRY VARCHAR2(12),
[Code] ......
I think the error is to do with the Athletes table (primary/foreign key)
How would I fix this in order to insert values without error?
View 3 Replies
View Related
Feb 4, 2015
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.
View 1 Replies
View Related
Jan 9, 2007
Hi all,
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 ??
Any assistance appreciated
View 1 Replies
View Related
Mar 12, 2008
Hi All,
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.
View 2 Replies
View Related
