똑똑...안녕하세요
SELECT name_id, tion,
split(tion,"-")[1] `aa`, split(tion,"-")[2] bb
from d_sn.dpsn
WHERE dt = '20200601'
-- 컬럼 tion 원본 data는 20-750-11 이런식으로 표현되나,
마지막 값 11 -> 2로 산출방법 좀 알려주시면 감사하겠습니다
공식은 (tion-3)/4 = 2
tion(컬럼명) | 공식 | 리턴값 |
3 | =(tion-3)/4 | 0 |
7 | =(tion-3)/4 | 1 |
11 | =(tion-3)/4 | 2 |
15 | =(tion-3)/4 | 3 |
19 | =(tion-3)/4 | 4 |
23 | =(tion-3)/4 | 5 |
-- 사용하는 DBMS 에 따라 구문이 다릅니다. -- Oracle 예제 -- WITH dpsn AS ( SELECT 1 name_id, '20200601' dt, '20-750-3' tion FROM dual UNION ALL SELECT 2, '20200601', '20-750-7' FROM dual UNION ALL SELECT 3, '20200601', '20-750-11' FROM dual UNION ALL SELECT 4, '20200601', '20-750-15' FROM dual UNION ALL SELECT 5, '20200601', '20-750-19' FROM dual UNION ALL SELECT 6, '20200601', '20-750-23' FROM dual ) SELECT name_id , tion , SUBSTR(tion, INSTR(tion, '-', -1) + 1) x , (SUBSTR(tion, INSTR(tion, '-', -1) + 1) - 3) / 4 y FROM dpsn WHERE dt = '20200601' ;