-
Identical names List
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
OCP(10g/9i/8i/8)
-
Any suggestions??????????
Agasimani
OCP(10g/9i/8i/8)
-
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
Last edited by hrishy; 01-27-2003 at 01:42 AM.
-
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
-
Does Anyone Recall Soundex ?
Can we apply here ?
Regards
Viraj
------------
OCP 9i DBA
A Wise Man Knows How much he doesn't know !!!
-
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
Last edited by hrishy; 01-27-2003 at 02:27 AM.
-
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.
Agasimani
OCP(10g/9i/8i/8)
-
Hi agasimani
well you get the idea !! thats cool man..i am sure you can build it from here on..
regards
Hrishy
-
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.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Hi Sanjay G
That was cool..guess time for attachements..i gues ;-D
regards
Hrishy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|