오라클에서는 아래와 같은 쿼리로 IP adress 를 DOT(.) 기준으로 분리할수 있습니다.
SCOTT@ORA11GR2>
WITH X
AS (SELECT '192.168.0.1' ip FROM DUAL)
SELECT
ip,
SUBSTR(ip, 1, INSTR(ip, '.') - 1) a,
SUBSTR(ip, INSTR(ip, '.') + 1, INSTR(ip, '.', 1, 2) - INSTR(ip, '.') - 1) b,
SUBSTR(ip, INSTR(ip, '.', 1, 2) + 1, INSTR(ip, '.', 1, 3) - INSTR(ip, '.', 1, 2) - 1) c,
SUBSTR(ip, INSTR(ip, '.', 1, 3) + 1) d
FROM X;
IP A B C D
----------- ---------------------- ---------------------- ---------------------- ----------------------
192.168.0.1 192 168 0 1
16:30:37 SCOTT@ORA11GR2>
PostgreSQL 에서는 어떻게 구현할수 있을까요?
split_part 함수를 이용하면 됩니다.
scott@cloud-00:5432:scottdb]
SQL> WITH X AS
(
SELECT CAST('192.168.0.1' AS TEXT) ip
)
SELECT ip,
split_part(ip,'.',1) AS a,
split_part(ip,'.',2) AS b,
split_part(ip,'.',3) AS c,
split_part(ip,'.',4) AS d
FROM X;
ip | a | b | c | d
-------------+-----+-----+---+---
192.168.0.1 | 192 | 168 | 0 | 1
(1 row)
참고로 아래와 같이 종으로 보여지게 할수도 있습니다. 그것도 아주쉽게 말이죠^^
scott@[local]:5432:scottdb]
SQL> select unnest(string_to_array('192.168.0.1','.')) as x;
x
-----
192
168
0
1
(4 rows)
scott@[local]:5432:scottdb]
SQL>
