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

Checking For Transposed Numbers

Jul 20, 2005

Does anyone have a UDF or Stored Procedure that checks for transposednumbers in a group?

View 2 Replies View Related

Table Copy Misses Indexes

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

Fuzzy Lookup Misses Possible Match

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

Where Can I Find The Import Data/Export Data Options If I Only Have The SQL Server Management Express Studio?

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

Find That Data

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

Find Data Location

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

Using Cursors To Find Data

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

Find Out All Data About Table

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

How To Find Data Mining 's Example

Jun 7, 2007

how to find data mining 's example from sql2005

how to apply data mining in project

View 3 Replies View Related

Cant Find Data Source

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

How To Find Data Is In A Table?

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

Find Out Which Table Columns Data Comes From

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

How Do I Find Data Having Max Length In The Database

May 18, 2006

How do I find data having max length in the Database ?

View 1 Replies View Related

How To Find Text/data In All Of My Tables?

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

How Can We Find When The Data File Is Full?

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

Find Table And Column Name From Data

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

Find How Much Of Partition Filegroup Contains Data?

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

Find Data Within 10m Distance Of Coordinates?

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

Find Missing Data From Table

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

Please Help!! How To Find Space At End Of Data In A Column

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

Query Doesn't Find Existing Data

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

Query Doesn't Find Existing Data

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

How To Find Who Delete / Truncate Data From Table

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

Query For Find Data Last Inserted In A Table

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

Any Way To Find Out Which SP Is Updating Data In A Specific Table?

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

How Do I Find Out When A Data Driven Subscription Ends?

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

Find The Last Time The Data In A Particular Table Was Changed

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

Find Existing Data Within A Date Range Change

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

Comparing Data In Two Tables To Find Missing Records

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

Data Access :: How To Find Column Names In A Table

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

Transact SQL :: Using Regular Expression To Find And Replace Data

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

Data Warehousing :: Find CDC Enabled Time In Database

Aug 11, 2015

How to find the CDC enabled date and time  in database.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved