我必须在oracle开发人员的单列中找到重复的字符串 [英] I have to find a duplicate strings in single column in oracle developer

问题描述

我有桌子像

ID姓名

1 John son

2 john john

3 tesera teresa



我要结果如

ID名称

2 john john

3 tesera tesera

任何人都可以帮助我



我尝试过：

 SELECT NAME，COUNT（NAME）
 FROM TEST_V 
 GROUP BY NAME 
 HAVING COUNT（NAME）> 1 

解决方案

问题是你的名字不是重复的：或者如果它们是，它们的存储很糟糕。

名称是单列，因此为了比较两半是否相等，您必须在空格之前和之后拆分它，然后比较这两个部分。你可以做到这一点，但它的效率非常低 - 字符串处理不是Oracle强项。

如果你需要将这两个名称视为单独的名称，那么将它们存储为单独的名称（forename，surname）并且比较那些。

SQL全局 - 而且Oracle在字符串处理方面也很差......

在你的情况下你必须找到一种方法来分割白色空格字符周围的名称，然后比较，看看是否有重复的部分...

（考虑到并非所有的名称都只由两部分组成 - 例如我有3个部分）

您在那里的代码与您的要求无关 - 如果在不在单个字段内的记录中发现重复值...

所以，如果你不能跳过这个要求 - 在代码中而不是在SQL中进行（在你完成时，几乎不可能创建一个通用的比较方法，你会发现它的表现非常差）...



- 编辑

这里有一些SQL（不是O. racle tests）可以分割一个字符串并创建一个表格，其中每个部分都是一行...您可以创建一个基于它的函数调用并使用标准SQL测试它的重复...但要注意 - 它的性能非常糟糕...

  DECLARE   @ NAME   AS   NVARCHAR （MAX）= ' < span class =code-string> jhon jhon terry perry' 
  DECLARE   @ NAME_PART  表 
（
 PART  NVARCHAR （MAX）
 ）
 
  DECLARE   @ CURVAL   AS   NVARCHAR （MAX）
  DECLARE   @ALLVALS  < span class =code-keyword> 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 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

解决方案

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.

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...

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

这篇关于我必须在oracle开发人员的单列中找到重复的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！