Identical names List

[agasimani]

I have a table as follows:

row name x y
-------------------------------------
1 abc and co 9 5
2 xyz est 3 2
3 queen hotel 2 9
4 abc & co 9 5
5 king's company 4 2
6 xyz establishment 3 2

As you can see in the table, row 1 and 4 are similar, but for some spelling difference and so are row 2 and 6.

I want to write a script which will give me list of such names whose names are almost identical and have same x and y values. Any hlep?

[agasimani]

Any suggestions??????????

[hrishy]

Hi Agsimani

please have a look below...

SQL> select * from agsimani
2 /

ROW_NUM COL1
---------- -------------------------
1 abc and co 9 5
2 xyz est 3 2
3 queen hotel 2 9
4 abc & co 9 5
5 king company 4 ,2
6 xyz establishment 3 2

6 rows selected.

SQL> select t1.row_num,t1.col1,t2.col1,t2.row_num
2 from agsimani t1, agsimani t2
3 where
4 substr(t1.col1,1,3) like substr(t2.col1,1,3)
5 and
6 t1.row_num<>t2.row_num
7 /


ROW_NUM COL1 COL1 ROW_NUM
-- ---------------------- ---------------------- -------
4 abc & co 9 5 abc and co 9 5 1
6 xyz establishment 3 2 xyz est 3 2 2
1 abc and co 9 5 abc & co 9 5 4
2 xyz est 3 2 xyz establishment 3 2 6

[hrishy]

Hi agsimani

oops dude i am not able to preserve the nice formatting when i make the post..

LOL..

regards
Hrishy

but you have my sql you can try yourself ;-D

[virajvk]

Does Anyone Recall Soundex ?
Can we apply here ?

Regards
Viraj
------------
OCP 9i DBA

[hrishy]

Hi

SQL> select t1.row_num,t1.col1,t2.col1,t2.row_num
2 from agsimani t1, agsimani t2
3 where
4 soundex(t1.col1) = soundex(hardcode)
5 and
6 t1.row_num<>t2.row_num
7 /

so soundex is not the solution...


regards
Hrishy

[agasimani]

Hi hrishy,

Though your query is not exactly what i was looking for, but I got an idea from this to fix the issue. Thanx buddy.

[hrishy]

Hi agasimani

well you get the idea !! thats cool man..i am sure you can build it from here on..

regards
Hrishy

[SANJAY_G]

Originally posted by hrishy
Hi agsimani

oops dude i am not able to preserve the nice formatting when i make the post..

LOL..

regards
Hrishy

but you have my sql you can try yourself ;-D

Hi Hrishy,

http://www.dbasupport.com/forums/mis...aq&page=3#HTML
Hope this helps.

[hrishy]

Hi Sanjay G

That was cool..guess time for attachements..i gues ;-D

regards
Hrishy