ORACLE SQL query to find data which are similar, but not exactly same

Question

I am trying to search for data which are similar, but not exactly same, means that the data is not total duplicate.

I need to find the data for the column value PROJECTNAME,from the record PSPROJECTITEM, which contains two fields like this, AZ_HCM_901 and AZ_HCM_901_BKP.

So the field AZ_HCM_901 and AZ_HCM_901_BKP are similar, even the column value of these two fields are same, but they are not exact duplicate, i need a query to retrieve the values which are similar, and are separated by _BKP in the table along with the orignial.

!Table structure 1

And this is how the value looks like:

**AZ_HCM_901_BKP 0 1 ADDRESSES 0 0 0 0 4 4 3 1 1

AZ_HCM_901 0 1 ADDRESSES 0 0 0 0 4 4 3 1 0**

So we can see that the values are similar except for the column PROJECTNAME.

Also, AZ_HCM_901 is an example here, table contains the PROJECTNAME like this. I want to retrieve the values which are exactly like this, one original and one separated by _BKP

I want the data be in separate rows.

Thanks for the help.

score 0 · Answer 1 · answered Jun 07 '13 at 06:48

0

SELECT proj_1.*, proj_2.*
FROM PSPROJECTITEM proj_1
     INNER JOIN PSPROJECTITEM proj_2 ON concat(proj_1.projectname, '_BKP') = proj_2.projectname;

answered Jun 07 '13 at 06:48

TrongBui

31
5

Thanks for the response,I will accept this answer in a short while, there is a slowdown of network here :). – vamosrafa Jun 07 '13 at 07:00
did you mean the whole rows similiar or just the project name? If it is just the projectname then an order statement can help ^^. – TrongBui Jun 07 '13 at 07:51
Whole rows similar, and project name almost similar, and only separted by _bkp in the table. thanks – vamosrafa Jun 07 '13 at 08:01
I think this might help http://stackoverflow.com/questions/921978/fuzzy-matching-using-t-sql – TrongBui Jun 07 '13 at 08:06
with order statement, we will be able to separate the rows right? Thanks – vamosrafa Jun 07 '13 at 08:27

ORACLE SQL query to find data which are similar, but not exactly same

1 Answers1