Home » SQL & PL/SQL » SQL & PL/SQL » Comparing similar strings in a table (Oracle 10g)
Comparing similar strings in a table [message #355226] Thu, 23 October 2008 03:29 Go to next message
aditi2008
Messages: 9
Registered: May 2008
Junior Member
I have a table called identifier_details with around 135K recs.
From this table I need to delete 1 of the records with similar product name and artist name
and also keep the one that have a different product name and artist name mapped to the same identifier.
So my data is something like this:
ID IDENTIFIER PRODUCT_NAME ARTIST_NAME
------- ------------ ----------------- ----------
111 --- SS123 --- All My People --- Lil iROCC
222 --- SS123 --- I-R-O-C-C --- Lil iROCC
333 --- SS123 --- IROCC --- Lil iROCC

And i want to look the data like this:
ID IDENTIFIER PRODUCT_NAME ARTIST_NAME
------- ------------ ----------------- ----------
111 --- SS123 --- All My People --- Lil iROCC

333 --- SS123 --- IROCC --- Lil iROCC

Any help on this will be appreciated.
Re: Comparing similar strings in a table [message #355237 is a reply to message #355226] Thu, 23 October 2008 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you already tried.
Use SQL*Plus and copy and paste your session.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Comparing similar strings in a table [message #355238 is a reply to message #355226] Thu, 23 October 2008 03:43 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


From this table I need to delete 1 of the records with similar product name and artist name 



It depends on how you define the similarity .

Smile
Rajuvan.
Re: Comparing similar strings in a table [message #355245 is a reply to message #355226] Thu, 23 October 2008 03:59 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Hello Aditi

can you give us exact details where you are struck with ?..

tell us your formatted code,. we may correct you ...




Thanks
Seyed Mohamed.
Re: Comparing similar strings in a table [message #355252 is a reply to message #355245] Thu, 23 October 2008 05:12 Go to previous messageGo to next message
aditi2008
Messages: 9
Registered: May 2008
Junior Member
@Rajuvan,
Well I do not have any definition of similarity.Its just that they need to look similar.

@Mohammed,
I don't know how to approach this problem.
So this is the only requirement I have got as of now.
But any pointers as to how to get started can prove to be fruitful.
Re: Comparing similar strings in a table [message #355260 is a reply to message #355252] Thu, 23 October 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Well I do not have any definition of similarity.Its just that they need to look similar.

Well come back when you'll know what you want to ask and stop wasting other's time.

Regards
Michel
Re: Comparing similar strings in a table [message #355283 is a reply to message #355260] Thu, 23 October 2008 06:08 Go to previous messageGo to next message
aditi2008
Messages: 9
Registered: May 2008
Junior Member
Noted.
Anyways I got the solution to my problem.
Re: Comparing similar strings in a table [message #355298 is a reply to message #355283] Thu, 23 October 2008 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which is?

Regards
Michel
Re: Comparing similar strings in a table [message #355299 is a reply to message #355226] Thu, 23 October 2008 06:38 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


If you got the solution , Please Post it for other's reference .
That is the real spirit Smile

Smile
Rajuvan.
Re: Comparing similar strings in a table [message #355320 is a reply to message #355252] Thu, 23 October 2008 07:56 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
aditi2008 wrote on Thu, 23 October 2008 06:12

Well I do not have any definition of similarity.Its just that they need to look similar.



I think NASA or the NSA might have a computer powerful enough to use Artificial Intelligence to solve your problem, but based on your last response, you built something yourself. You may want to sell that program to friendly governments.
Re: Comparing similar strings in a table [message #355337 is a reply to message #355283] Thu, 23 October 2008 14:40 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
http://img2.mysmiley.net/imgs/smile/animated/anim_63.gif

I wonder if OP achieved the result via SOUNDEX Function.

Got an Example here

Regards,
Jo
Previous Topic: SQL query
Next Topic: SQL with CASE in Trigger
Goto Forum:
  


Current Time: Sat Dec 10 10:40:05 CST 2016

Total time taken to generate the page: 0.10493 seconds