데이터베이스 질문과 답변
SELECT table_name ,
index_name ,
CASE
WHEN REGEXP_LIKE(index_name, table_name || '^_IX[0-9]')
THEN 'GOOD'
WHEN REGEXP_LIKE(index_name, table_name || '^_IX0[0-9]')
THEN 'BAD'
ELSE '?'
END AS "GOOD or BAD"
FROM (
SELECT 'TABLE1' table_name ,
'TABLE1_IX1' index_name
FROM dual
UNION ALL
SELECT 'TABLE2' table_name ,
'TABLE2_IX01' index_name
FROM dual
)
-- 위 쿼리 결과값
TABLE_NAME INDEX_NAME GOOD or BAD
---------- ----------- -----------
TABLE1 TABLE1_IX1 ?
TABLE2 TABLE2_IX01 ?
-- 원하는 결과값
TABLE_NAME INDEX_NAME GOOD or BAD
---------- ----------- -----------
TABLE1 TABLE1_IX1 GOOD
TABLE2 TABLE2_IX01 BAD
by 우리집아찌
[2018.08.01 09:58:04]
SELECT table_name ,
index_name ,
CASE WHEN REGEXP_LIKE(index_name, table_name || '_IX0' || '[0-9]') THEN 'BAD'
WHEN REGEXP_LIKE(index_name, table_name || '_IX' || '[0-9]') THEN 'GOOD'
ELSE '?'
END AS "GOOD or BAD"
FROM (
SELECT 'TABLE1' table_name ,
'TABLE1_IX0' index_name
FROM dual
UNION ALL
SELECT 'TABLE2' table_name ,
'TABLE2_IX01' index_name
FROM dual
UNION ALL
SELECT 'TABLE2' table_name ,
'TABLE2_IX07' index_name
FROM dual
)
by 오라오라
[2018.08.01 10:01:48]
아 ! ㅠㅠ 감사합니다 !!!
by 아발란체
[2018.08.01 10:07:02]
참고용으로 다른 방법도 하나 올리면
WITH T AS (
SELECT 'TABLE1' AS t_nm, 'TABLE1_IX1' AS i_nm FROM DUAL
UNION ALL SELECT 'TABLE1' AS t_nm, 'TABLSAETE' AS i_nm FROM DUAL
UNION ALL SELECT 'TABLE2', 'TABLE2_IX01' FROM DUAL
)
SELECT
t_nm, i_nm,
CASE REGEXP_INSTR(i_nm, '(0|IX)[1-9]$')
WHEN(LENGTH(i_nm) - 1) THEN 'BAD'
WHEN(0) THEN '?'
ELSE 'GOOD'
END AS result
FROM
T
) 버튼을 클릭하여 작성 하시면 됩니다.