从Pattern中查找字符最少的匹配记录-Oracle/Java [英] Find matching records with least characters from Pattern - Oracle / Java

问题描述

我正在使用的Web应用程序当前具有文件导入逻辑.逻辑

The web application I am working currently has an File import logic. The logic

1>从文件[excel或txt]中读取记录，
2>显示导入的所有记录的不可编辑网格[如果数据库中不存在新记录，而现有记录被标记为Update，则将新记录标记为New]和
3>将记录转储到数据库中.

1> reads the records from a file [excel or txt],
2> shows a non editable grid of all the records imported [New records are marked as New if they do not exist in the database and existing records are marked as Update] and
3> dumps the records in the database.

文件中包含以下格式的联系人的文件(使用主键First_Name, Last_Name镜像数据库中的列):

a file containing contacts with following format in the file (mirrors the columns in the database with primary keys First_Name, Last_Name):

First_Name, Last_Name, AddressLine1, AddressLine2, City, State, Zipcode

我们遇到的问题是，在文件中输入的相同实体具有不同的值.例如，有人可能会在纽约键入NY，而其他人会在纽约键入.名字或姓氏ex相同.约翰·迈尔斯(John Myers)和约翰·迈尔(John Myer)指的是同一个人，但是由于记录不完全匹配，因此插入记录而不是将其重复用于更新.

The issue we are running into is when there are different values for the same entity being entered in the file. example, Someone might type NY for New York while others would put in New York. Same applies to first name or last name ex. John Myers and John Myer refer to the same person, but because the record does not match exactly, it inserts the record rather than reusing it for an update.

例如，对于文件中的记录(请注意，名称和地址的使用纯属巧合:)):

Example, for the record from the file (Please note the name and address usage is purely coincidental :) ):

John, Myers, 44 Chestnut Hill, Apt 5, Indiana, Indiana, 11111

以及数据库中的记录:

John, Myer, 80 Washington St, Apt 1, Chicago, IL, 3333

系统应该已将文件中的记录检测为现有记录(由于姓氏为Myers和Myer，并且由于名字完全匹配)，并且对地址进行了更新，而是插入了一个新的值.

the system should have detected the record in the file as existing record [because of the last name being Myers and Myer and since first name matches completely] and do an update on the Address, but rather inserts a new value.

在要查找将对数据库中的现有记录执行匹配的所有记录的情况下，如何处理此问题?

How can I approach this issue where I would want to find all the records that would perform the match on the existing records in the database?

推荐答案

我能想到的一个解决方案是在Oracle中使用Regex在某种程度上实现功能.

One solution I could think of is using Regex in Oracle to achieve the functionality upto some extent.

对于每一列，我将在String长度的一半处生成Regex表达式.例如，对于文件中的名称"Myer"和数据库中的名称"Myers"，以下查询将起作用:

For each of the column, I would generate Regex expression half way through the String length. example, for the name "Myer" in the file and "Myers" in the database, following query would work:

SELECT Last_Name from Contacts WHERE (Last_Name IS NULL OR Regexp_Like(Last_Name, '^Mye?r?$'))

我将其视为部分解决方案，因为我将解析输入字符串，并开始将none or only one运算符从字符串的一半长度添加到字符串的末尾，并希望输入字符串不会被弄乱.

I would consider this as a partial solution because I would parse the input string and start appending the none or only one operator from half the length to the end of the string and hoping the input string is not so messed up.

希望从其他人那里获得有关此解决方案"的一些反馈.

Hoping to find some feedback from others on SO for this "solution".

这篇关于从Pattern中查找字符最少的匹配记录-Oracle/Java的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！