CODEWITHSUNDEEP
sqloracle-databaseoracle10g
shocky shocky
shocky shocky

Reputation: 55

remove duplicate values from a oracle sql query's output

I have a situation where I want to remove the duplicated record from the result by using sql query in oracle 10g. I am using regular expression to remove the alphabets from the result

Original value = 1A,1B,2C,2F,4A,4z,11A,11B

Current Sql query 

select REGEXP_REPLACE( tablex.column, '[A-Za-z]' , '' )
from db1

gives me the following output

1,1,2,3,4,4,11,11

how can i remove duplicate from the output to just show unique values i.e.

1,2,3,4,11

Upvotes: 2

Views: 3262

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16905

Assuming that your table contains strings with values separated with commas.

You can try something like this:
Here is a sqlfiddle demo

select rtrim(xmltype('<r><n>' || 
               replace(REGEXP_REPLACE( col, '[A-Za-z]' , '' ), ',', ',</n><n>')||',</n></r>'
              ).extract('//n[not(preceding::n = .)]/text()').getstringval(), ',')
from tablex;

What it does is after using your regexp_replace it makes a xmltype from it and then uses XPATH to get the desired output.

If you also want to sort the values (and still use the xml approach) then you need XSL

select rtrim(xmltype('<r><n>' || 
               replace(REGEXP_REPLACE( col, '[A-Za-z]' , '' ), ',', '</n><n>')||'</n></r>'
              ).extract('//n[not(preceding::n = .)]')
.transform(xmltype('<?xml version="1.0" ?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"><xsl:for-each select="//n[not(preceding::n = .)]"><xsl:sort select="." data-type="number"/><xsl:value-of select="."/>,</xsl:for-each></xsl:template></xsl:stylesheet>'))
.getstringval(), ',')              
from tablex;

But you can also try different approaches, such as splitting the tokens to rows and then recollecting them

select rtrim(xmlagg(xmlelement(e, n || ',') order by to_number(n))
              .extract('//text()'), ',')
from(
SELECT distinct rn, trim(regexp_substr(col, '[^,]+', 1, level)) n
  FROM (select row_number() over (order by col) rn ,
               REGEXP_REPLACE( col, '[A-Za-z]' , '' ) col
        from tablex) t
CONNECT BY instr(col, ',', 1, level - 1) > 0
)
group by rn;

Upvotes: 3

Related Questions