How To Find Transposed Data And Near Misses
Jul 23, 2005
I would like some advice on a data and query problem I face. I have a
data table with a "raw key" value which is not guaranteed to be valid
at its source. Normally, this value will be 9 numeric digits and map to
a "names" table where the entity is given assigned an "official name".
My problem is that I'd like to be able to identify data values that are
"close" to being "correct". For example, in the case of a
nine digit number such as 077467881, I'd like to be able to identify
rows with values close to this raw string. That is, if
there were a row with a value for this column that was "off" by say, a
transposed single digit (such as 077647881 in this example)
I would like to find a query to locate the "close candidates" in a
result set. If I can find rows having a raw key
value that is close to a "good key" then I can allow my user to use
other criteria to possibly assign the "close key" as
an alternate or alias of the official key. Here is part of my schema:
CREATE TABLE MYData (
StateCD char (2) NOT NULL ,
CountyCD char (3) NOT NULL ,
MYID int NULL ,
RawNumString varchar(9) NULL ,
SaleMnYear datetime NOT NULL ,
NumberWidgets int NOT NULL ,
)
CREATE TABLE MYNames (
MYID int IDENTITY (1, 1) NOT NULL ,
OfficialName varchar (70) NOT NULL ,
CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED
(
MYID
)
)
CREATE TABLE MYAltID (
RawNumString varchar (9) NOT NULL ,
MYID int NOT NULL ,
CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED
(
RawNumString
) ,
CONSTRAINT FK_HasName FOREIGN KEY
(
MYID
) REFERENCES MYNames (
MYID
)
)
So, how to generalize something like:
SELECT * FROM MYData WHERE RawNumString = '077467881'
OR RawNumString = '077647881'
View 6 Replies
ADVERTISEMENT
Jul 20, 2005
Does anyone have a UDF or Stored Procedure that checks for transposednumbers in a group?
View 2 Replies
View Related
Sep 9, 2005
Hi,I simply want to create a duplicate of an existing table with adifferent name. I've tried the "select * into newtable fromorgtable", which works great, except that it doesn't mark the primarykey field from orgtable in the newtable. I tried creating thenewtable prior to using this select, but then sql server cries aboutthe table already existing. I tried creating the index after theselect "CREATE UNIQUE INDEX FieldAIndex ON newtable (FieldA)" - thatdidn't give a error, but also failed to mark the field as primary.Suggestions?
View 2 Replies
View Related
Apr 10, 2008
I have a fuzzy lookup task that compares a source list of contacts to a reference list of contacts with the default settings. I did some testing by adding seed data that I knew would produce somewhat high similarity hits. All of the seeded contacts but one came back with the expected high sim values. When I looked for the one that didn't, I noticed another match had come up but it had a very low similarity of .17. I then did some research and discovered the reason was the MaxOutputMatchesPerInput setting which was set to 1. I then set it to 3 and reran the package and sure enough my seeded contact that was missing before now showed up. I thought the best match would show up if the MaxOutputMatches was set to 1? That is not the case in my testing.
For example, Donna Mizeman was in the reference list. I added Don Miseman to the source list to seed it. The only match that came back was something like Dieman Abdul .... So the initial match had a similarity of .17 but when MaxOutputMatchesPerInput is set to 3 the best match (seeded) has a similarity of .72.
Anyone have an explanation for this?
-Mike
View 1 Replies
View Related
Oct 4, 2007
Hi all,
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
Your prompt response is greatly appreciated.
Thanks!!
Tram
View 8 Replies
View Related
Jun 18, 2007
Hi ALL
Just wondering is there an easy way to search every table and every column for a specific piece of data in sql 2000?
Any ideas would be appreciated
Cheers
Phil
View 2 Replies
View Related
Sep 26, 2003
Hi,
I wanted to find the location in database objects(table/stored proc/view/function) for particular text.
For example a text 'BU1032' is located in tiles and titleathor table at PUBS databse.
I wanted get the result like
text_name filed_name table_name
--------- --------- ---------
BU1032 title_id titles
BU1032 title_id titleauthor
if we are using in stored proc/view for comparison that should be display
like
text_name storedproc_name
-------- ---------------
BU1032
Anybody has script for this kind of search?.
Thanks,
Ravi
View 6 Replies
View Related
May 30, 2008
What I would like to do, is loop through a table, extract two fields from each record, and pass the values of those fields to a stored proc. I found some help on using cursors to do this, but can I use two cursors at the same time? Below is what I would like to do:
DECLARE @cursorPhone CURSOR, @cursorName cursor, @phone VARCHAR(30), @UserName varchar(50)
SET @cursorPhone = CURSOR
FOR
SELECT userPhone FROM dbo.RPM_Data
OPEN @cursorPhone
FETCH NEXT FROM @cursorPhone INTO @Phone
SET @cursorName = CURSOR
FOR
SELECT userName FROM dbo.RPM_Data
OPEN @cursorName
FETCH NEXT FROM @cursorName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_Subscription_Individual @phone, @UserName
FETCH NEXT FROM @cursorPhone INTO @phone
FETCH NEXT FROM @cursorName INTO @Username
END
DEALLOCATE @cursorPhone
DEALLOCATE @cursorName
View 2 Replies
View Related
Apr 17, 2015
is it possible to find out all the data about the table so I need Table columns, type etc.
View 1 Replies
View Related
Jun 7, 2007
how to find data mining 's example from sql2005
how to apply data mining in project
View 3 Replies
View Related
Oct 15, 2007
Hi,
I use Visual Studio 2005 Pro.
I installed Microsoft SQL Server Compact Edition Developer Software Development Kit .
When i try to make a connection , i can't change the datasource to the compact version.(Not vissible)
What could be the problem.
Sorry for my bad english,
Thx
JoskeXP
View 3 Replies
View Related
Nov 15, 2007
I got a table in sql server....
i don't know the field name where data is present.
i just want to search and find, where data is present in any field of that table.
How can i do?. I forgot that SQL.
Please help me.
View 4 Replies
View Related
May 9, 2008
TableName: EmployeeCloumns: EmployeeID EmployeeName If I do "Select * From Employee", is there a way to do something like this...
Loop through each result IterateThroughControls.ID &= dbTable & dbColumnName & "<br />"EndSo that the end result would assign ID values...EmployeeEmployeeIDEmployeeEmployeeName
View 5 Replies
View Related
May 18, 2006
How do I find data having max length in the Database ?
View 1 Replies
View Related
Jan 18, 2000
Hello,
I have a quick question. Can you please tell me how to find/search a string/value in all of my tables in a database.
For example I need to find a value/data "GH45678", where-ever it appears in a database.
I would really appreciate your prompt response. I would also like to thank you very much in advance for your time/help.
Note: I need to find specific data in all of my tables, I am looking for something like text search in my database. I am using SQL Server 6.5. Thanks
View 1 Replies
View Related
May 7, 2002
I need to find out when the data file and transaction log file is full. Is there any stored procedure that will let how much space left. We don't want to set Autogrow for the files.
Thanks,
Rau
View 2 Replies
View Related
Jun 14, 2007
I need to find a table and column name from some given data. I know what data i want to edit, I just need to know where it is located and the database is too big to manually go through. It is Microsoft sql server 2000. Any help is appreciated.
View 9 Replies
View Related
Oct 7, 2015
I have partitions that I have filled with data. I am not trying to figure out exactly how much data the partitions contain, and therefore I will be able to see if any of them are close to hitting their autogrow conditions. If I were looking at a single unpartitioned table, then I could maybe look at the table properties to determine data and index sizes, and compare that to the size of the mdf file size, but for partitions, then I am not sure how I would query this information out. Any pointers on how this information could be queried out of the system?
View 3 Replies
View Related
Nov 22, 2013
I am trying to write a piece of SQL which gives me a list of enquiries within 10 metre distance of a enquiry.
The idea is to identify possible duplicates.
Table: enquiry
Primary key: enquiry_number
Co-ordinates data fields: enquiry.enquiry_easting and enquiry.enquiry_northing.
I will need to self-search on the same table to find possible enquiries within 10m distance.
View 1 Replies
View Related
Jul 23, 2005
Here is an issue that has me stumped for the past few days. I have atable called MerchTran. Among various columns, the relevant columns forthis issue are:FileDate datetime, SourceTable varchar(25)SQL statement:SELECT DISTINCTFileDate, SourceTableFROMMerchTranORDER BYFileDate, SourceTableData looks like this:FileDate DataSource-----------------------------------2005-02-13 00:00:00.000S12005-02-13 00:00:00.000S22005-02-13 00:00:00.000S32005-02-14 00:00:00.000S12005-02-14 00:00:00.000S22005-02-14 00:00:00.000S32005-02-15 00:00:00.000S22005-02-15 00:00:00.000S32005-02-16 00:00:00.000S12005-02-16 00:00:00.000S22005-02-16 00:00:00.000S32005-02-17 00:00:00.000S12005-02-17 00:00:00.000S22005-02-18 00:00:00.000S12005-02-18 00:00:00.000S22005-02-18 00:00:00.000S32005-02-19 00:00:00.000S12005-02-19 00:00:00.000S3We run a daily process that inserts data in to this table everyday forall 3 sources S1, S2, S3Notice how some data is missing indicating the import process for thatparticular source failed.Example: Missing record2005-02-15 00:00:00.000S12005-02-17 00:00:00.000S32005-02-19 00:00:00.000S2Can someone please help me with a SQL Statement that will return me the3 missing records as above.Thanks in advance for all your help!DBA in distress!Vishal
View 4 Replies
View Related
May 14, 2008
I need a query that checks each row in a column (hardness) to see if the last character is a "space". The column holds numbers (73.45) but is a varchar 50 type. It will always be the 5th character but it would be nice to not make this assumption. Please help!!
Thanks.
View 5 Replies
View Related
Jul 6, 2004
SQL Server 2000 Enterprise
While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
Ideas please.
View 1 Replies
View Related
Jul 6, 2004
SQL Server 2000 Enterprise
While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
Ideas please.
View 5 Replies
View Related
Sep 6, 2013
how to find who delete/Truncate the data from table ,because i don't have any trigger on the table.
View 5 Replies
View Related
Apr 9, 2015
I need to find the history in SQl server.
I need to find the data, When was data last inserted/updated/deleted in a specific table?. is it possible?
Table name: Employee
View 1 Replies
View Related
Sep 22, 2005
Can you create an UPDATE TRIGGER and use some typeof code to figure out which SP just updated the current table?If not how can i achieve what i want?I tried to run SQL Profiler and i don't understand why i can'tsimply have the Profiler filter events only for the specific database idand the table's object id i chose?What am i doing wrong with SQL Profiler? I was testing thisthrough SQL EM. I had the filters chosen for a specific database idand a specific table's object id, yet when i open another table SQLProfiler captures that information too.Thank you
View 2 Replies
View Related
Oct 10, 2007
Hello everybody,
I have a problem concerning the execution status of a data driven subscription. The problem is that
I´m creating a datamart and rendering several thousands of pdf based reports after that. I´ve found out how
to start the subscription using stored procedures with DTS.
Now I want to take further actions after the report building is done. But how do I find that out?
tfr
LG
View 3 Replies
View Related
Mar 27, 2008
Is there a Tool/Utility/StoredProcedure/Query/View that can tell me the last time the data in a particular table was changed?
That change can be the result of either an ADD , INSERT, or DELETE statement.
I am using SQL Server 2005.
View 3 Replies
View Related
Jul 30, 2014
We have records in one table that are marked as accepted/rejected based on eligibility start and end dates in another table. We're loading new eligibility data into an ETL table and if the start or end date is going to change, I want to report any records that need to be reviewed to see if their status should change. The new dates could be before or after the existing dates, and the new or existing end date could also be NULL. Currently I'm using 4 > < statements and it seems to catch any scenario, but I'm wondering if there's a better way:
DECLARE @RECORDS TABLE(RecordDate date,ID varchar(8))
INSERT INTO @RECORDS(RecordDate, ID)VALUES('20100101','99'),('20110101','99'),('20120101','99'),('20130101','99'),('20140101','99')
DECLARE @ORIGINALDATES TABLE(StartDate date,EndDate date,ID varchar(8))
INSERT INTO @ORIGINALDATES(StartDate,EndDate, ID)VALUES('20100101',NULL,99)
[code]....
View 1 Replies
View Related
Jul 20, 2005
I have two tables of book information. One that has descriptions of thebook in it, and the isbn, and the other that has the book title,inventory data, prices, the isbn.Because of some techncal constraints I won't get into now, I can'tcombine them both into one table. No problem. Things are going fine aslong as there is a description in the one table to corrispond to theisbn and other data in the other table.However, about half of the products are not yet entered into thedescrition table. I'd like to run a sql query that pulls up all theisbns that don't exist in the other. In other words, I'd like to get aquery that tells me exactly which isbns do not yet have descrition datain them. I know there is some sql that says to search from one filewhere the number does not exist in the other, but it slips my mind. Cansomeone help me on this please?Thank you!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Sep 11, 2015
how to find the columns names in a table in sql 2008r2.
as i need to compare the midsing fields in the table from two database.
View 3 Replies
View Related
Sep 9, 2015
I am writing an SQL query to find an replace data in a column. I have a table that is filled with
C:usersXXXXappdata
C:usersYYYYappda
C:usersZZZZZZappdata
I would like to replace the c:usersXXXXX part with %userprofile%
The end result would be %userprofile%appdata
I know how to dot the replacement in powershell. it's quite easy
-replace "c:users[^]+","%userprofile%"Â Â
Basically how would transfer this into SQL...
View 10 Replies
View Related
Aug 11, 2015
How to find the CDC enabled date and time in database.
View 3 Replies
View Related