I have to find a duplicate strings in single column in oracle developer

Question

1.00/5 (1 vote)

See more:

I have table like
ID Name
1 John son
2 john john
3 tesera teresa

I want result like
ID Name
2 john john
3 tesera tesera
can anyone help me

What I have tried:

 SELECT NAME, COUNT(NAME)
FROM TEST_V
GROUP BY NAME
HAVING COUNT(NAME) > 1

Posted 4-Sep-17 3:24am

Member 11416690

Updated 4-Sep-17 3:39am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-09-04T03:32:00

The problem is that your names aren't duplicates: or if they are, they are badly stored.
Name is a single column, so in order to compare the "two halves" for equality, you'd have to split it before and after the space, and then compare the two portions. You can do that, but it's really very inefficient - string handling is not an Oracle strong point.
If you need to treat these two as separate names, then store them as separate names (forename, surname perhaps) and compare those.

Kornfeld Eliyahu Peter · Answer 2 · 2017-09-04T03:39:00

SQL globally - and Oracle too is very poor at string handling...
In your case you have to find a way to split the name around white-space characters and then compare to see if there are repeated parts...
(Consider that not all names are made of two parts only - I have 3 of them for instance)
The code you have there has nothing to do with your requirement - if finds duplicate values across records not inside a single field...
So, if you can not skip this requirement - do it in code not in SQL (it is next to impossible to create a all-purpose compare method nad when you done, you will find it very poorly perform)...

-- EDIT
Here some SQL (not Oracle tested) that can split a string and create a table where every part is a row... You may create an call a function based on it and test it for duplicates using standard SQL... But beware - it has very bad performance...

SQL

DECLARE @NAME AS NVARCHAR(MAX) = 'jhon jhon terry perry'
DECLARE @NAME_PART TABLE 
(
	PART NVARCHAR(MAX)
)

DECLARE @CURVAL AS NVARCHAR(MAX)
DECLARE @ALLVALS AS NVARCHAR(MAX)

SET @ALLVALS = @NAME + ' '

WHILE LEN (@ALLVALS) > 0  
BEGIN 
	SET @CURVAL = LEFT(@ALLVALS, CHARINDEX(' ', @ALLVALS) - 1)

	INSERT INTO @NAME_PART SELECT @CURVAL

	SET @ALLVALS = RIGHT(@ALLVALS, LEN(@ALLVALS) - CHARINDEX(' ', @ALLVALS) + 1)
END

SELECT * FROM @NAME_PART

I have to find a duplicate strings in single column in oracle developer

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0