암호화후 복호화 하기 split_part사용

create extension pgcrypto;

 

select encode(encrypt(convert_to('홍길동','utf8'),'ENC_KEY2','aes'),'hex');

 

select convert_from(decrypt(decode('eca7e7bfbcd093229fb08e80fb0ee27f8368409e6acd8566c9c160766f897291d8f105397cf8410af16864f4099584cb','hex'),'ENC_KEY2','aes'),'utf8') as decrypt_id;

 

  select lgn_id,

         pswd,

         to_char(now(),'yyyymmddHH24MISS') as dt,

         encode(encrypt(convert_to( 'user_id#' || lgn_id || '#enc_dttm#' || to_char(now(),'yyyymmddHH24MISS') ,'utf8'),'ENC_KEY2','aes'),'hex') encrypt_id

from naqscom.ncom_id_mstr 

            where lgn_id = 'test'   

; 

 

 

-- 위치를 가지고 온다.

--start 9

select position('[' in 'user_id[test]enc_dttm[20210908163049]') + 1 as id

 

--end 12

select position(']' in 'user_id[test]enc_dttm[20210908163049]') as id

 

--end-start = 3 해당위치 아이디 가져온다.

select substr('user_id[test]enc_dttm[20210908163049]',9,12-9) as id

 

 

SELECT split_part('user_id#test#enc_dttm#20210908165456', '#', 2) as id,

       split_part('user_id#test#enc_dttm#20210908165456', '#', 4) as endDt

       ;