DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Identical names List

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304

    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)

  2. #2
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304

    Unhappy

    Any suggestions??????????
    Agasimani
    OCP(10g/9i/8i/8)

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    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.

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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

  5. #5
    Join Date
    Jan 2002
    Location
    India
    Posts
    105

    Does Anyone Recall Soundex ?
    Can we apply here ?

    Regards
    Viraj
    ------------
    OCP 9i DBA
    A Wise Man Knows How much he doesn't know !!!

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    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.

  7. #7
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304

    Thumbs up

    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)

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi agasimani

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

    regards
    Hrishy

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    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
  •  


Click Here to Expand Forum to Full Width