텍스트 파싱

텍스트 파싱

Occurred

SELECT LEFT(sighting_report, 8) AS left_digits, COUNT(*)
FROM ufo
GROUP BY 1;

SELECT RIGHT(LEFT(sighting_report, 25), 14) AS occurred
FROM ufo

split_part

SELECT SPLIT_PART(sighting_report, 'Occurred : ', 2) AS split_1
FROM ufo;

SELECT SPLIT_PART(sighting_report, ' (Entered', 1) AS split_2
FROM ufo;

(Entered를 기준으로 자른 첫 번째 문자열을 또 다시 Occurred : 를 기준으로 잘라서 두 번째 문자열을 가져온 것이다.

SELECT SPLIT_PART(SPLIT_PART(sighting_report, ' (Entered', 1), 'Occurred : ', 2) AS occurred
FROM ufo;

아래 사진처럼 다른 형식과는 다르게 Reported가 붙은 긴 문자열이 보인다.

14번째 행의 문자열도 다른 형식과 마찬가지로 처리 되었다.

SELECT SPLIT_PART(SPLIT_PART(SPLIT_PART(sighting_report, ' (Entered', 1), 'Occurred : ', 2), 'Reported', 1) AS occurred
FROM ufo;

occurred, entered, reported, posted, location, shape, duration

SELECT
	SPLIT_PART(SPLIT_PART(SPLIT_PART(sighting_report, ' (Entered', 1), 'Occurred : ', 2), 'Reported', 1) AS occurred,
	SPLIT_PART(SPLIT_PART(sighting_report, ')', 1), 'Entered as : ', 2) AS entered_as,
	SPLIT_PART(SPLIT_PART(SPLIT_PART(SPLIT_PART(sighting_report, 'Post', 1), 'Reported: ', 2), 'AM', 1), 'PM', 1) AS reported,
	SPLIT_PART(SPLIT_PART(sighting_report, 'Location', 1), 'Posted: ', 2) AS posted,
	SPLIT_PART(SPLIT_PART(sighting_report, 'Shape', 1), 'Location: ', 2) AS location,
	SPLIT_PART(SPLIT_PART(sighting_report, 'Duration', 1), 'Shape: ', 2) AS shape,
	SPLIT_PART(sighting_report, 'Duration:', 2) AS duration
FROM ufo;