druid druid - 4 months ago 6
SQL Question

How to list all entries in the column that exist as "devicex" and "devicex.domain.com on Oracle

There's a column "DeviceName" that stores devices in my tables "Devices", normally this column should only list unique devices but I found out that some of them appear as "Device1" AND "Device1.domain.com".

The list would look something like:

Device1
Device1.domain.com
Device2
Device3
Device4
Device4.domain.com
Device7
Device11
Device12
etc...


I need to list all devices that are in this case:

Device**1**
Device**1**.domain.com
Device**4**
Device**4**.domain.com
etc...


As far as I understand I should extract the first part of all DeviceName that contain a "." by stopping at the first "." and compare it to all the other DeviceName and output all those that exist twice.

I suppose that this should be achieved with but I looked at many sites (such as http://www.techonthenet.com/oracle/functions/regexp_substr.php) and I'm totally lost (I usually write pretty simple queries.

Any hint please?

Thanks in advance

Answer

This should give you all the duplicates:

select * from (
  select d.*, count(*) over (partition by substr(deviceName,1,decode(instr(deviceName,'.'),0,length(deviceName),instr(deviceName,'.')-1))) as rn 
    from devices d
   )
   where rn > 1
  ;

with your data I get the following output:

deviceName         rn
Device1.domain.com  2
Device1 2
Device4.domain.com  2
Device4 2
Comments