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
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;
select distinct REGEXP_REPLACE( tablex.column, '[A-Za-z]' , '' ) from db1. Share and enjoy.