SELECT ROUND(NVL(SUM(take_prc) over(PARTITION BY code),0),-8) AS ARP FROM TEST
위와같은 SELECT 형식으로 ARP가 1242500000000 이런식으로나옵니다
그런데 저는 8자리에서 반올림하고 나머지뒤에 0은 안보이게하고싶습니다 12425 만나오면되는데.. 문제는 앞자리수가 게속바뀌는구조라
substring으로 자르기가 힘들더라구요..
현제 데이터는 아래와같은데 -> 처럼 할려면 어떻게해야할까요.
고수님들의 조언부탁드리겠습니다
190900000000 -> 1909
1242500000000 -> 12425
2032600000000 -> 20326
1303400000000 -> 13034
35200000000 ->352
WITH T AS ( SELECT 190900000000 AS ARP FROM DUAL UNION ALL -- -> 1909 SELECT 1242500000000 FROM DUAL UNION ALL -- -> 12425 SELECT 2032600000000 FROM DUAL UNION ALL -- -> 203260 SELECT 1303400000000 FROM DUAL UNION ALL -- -> 13034 SELECT 35200000000 FROM DUAL -- ->352 ) SELECT ARP , REPLACE ( TO_CHAR(ARP) , '00000000' , '' ) AS VAL1 , REGEXP_REPLACE( TO_CHAR(ARP) , '[0]{8}$' , '' ) AS VAL2 FROM T