텍스트 변환

텍스트 변환

Shape

• Shape만을 추출해낸다.
• 문자의 첫 글자를 대문자로 변환한다.

SELECT DISTINCT shape, INITCAP(shape) AS shape_clean
FROM (
	SELECT SPLIT_PART(SPLIT_PART(sighting_report, 'Duration', 1), 'Shape: ', 2) AS shape
	FROM ufo
) AS A;

Duration

• Duration을 추출한다.
• 앞 뒤 공백을 제거한다.

SELECT duration, TRIM(duration) AS duration_clean
FROM (
	SELECT SPLIT_PART(sighting_report, 'Duration:', 2) AS duration
	FROM ufo
) AS A;

Occurred, Reported, Posted

SELECT
	SPLIT_PART(SPLIT_PART(SPLIT_PART(sighting_report, '(Entered', 1), 'Occurred : ', 2), 'Reported', 1) AS occurred,
	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
FROM ufo;

데이터 타입 변환

• 위 쿼리를 서브쿼리로 사용한다.
• 각 컬럼의 문자열 길이가 8개 이상인 것을 조건으로 사용한다.
• occurred를 오름차순으로 정렬한다.

<날짜 타입 변환>

• SET DATESTYLE = mdy;를 통해 현재 데이터 타입을 명시한다.
• ::TIMESTAMP 또는 ::DATE를 통해 변환할 데이터 타입을 설정한다.

-- 타입 변환 에러가 발생하지 않도록, 현재 저장된 문자열이 월/일/연도(month/day/year)의 포맷으로 저장되어 있음을 명시 
SET DATESTYLE = mdy;
SELECT
	occurred::timestamp,
	reported::timestamp,
	posted::date
FROM (
	SELECT
		SPLIT_PART(SPLIT_PART(SPLIT_PART(sighting_report, '(Entered', 1), 'Occurred : ', 2), 'Reported', 1) AS occurred,
		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
	FROM ufo
) AS A
WHERE LENGTH(occurred) >= 8
	AND LENGTH(reported) >= 8
	AND LENGTH(posted) >= 8
ORDER BY 1;

CASE 구문 사용하여 데이터 타입 변환

• 위 쿼리를 CASE 구문을 사용하여 같은 결과를 나타낼 수 있다.
• occurred 컬럼에는 NULL이 존재하므로 조건을 추가해준다.
• 나머지는 동일하게 문자열의 길이가 8보다 작으면 NULL로 표시한다.
• 8자리를 기준으로 설정한 이유는 6/2/2023 일 경우 LENGTH가 8이기 때문에 저 형식을 기준으로 한 것 같다.

-- 타입 변환 에러가 발생하지 않도록, 현재 저장된 문자열이 월/일/연도(month/day/year)의 포맷으로 저장되어 있음을 명시 
SELECT
	(CASE
		WHEN occurred = '' THEN NULL
		WHEN LENGTH(occurred) < 8 THEN NULL
	 	ELSE occurred::TIMESTAMP
	END) AS occurred,
	(CASE
	 	WHEN LENGTH(reported) < 8 THEN NULL
	 	ELSE reported::TIMESTAMP
	END) AS reported,
	(CASE
	 	WHEN LENGTH(posted) < 8 THEN NULL
	 	ELSE posted::TIMESTAMP
	END) AS posted
FROM (
	SELECT
		SPLIT_PART(SPLIT_PART(SPLIT_PART(sighting_report, '(Entered', 1), 'Occurred : ', 2), 'Reported', 1) AS occurred,
		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
	FROM ufo
) AS A
ORDER BY 1;

Location

SELECT
	location,
	REPLACE(REPLACE(location, 'close to', 'near'), 'outside of', 'near') AS location_clean
FROM (
	SELECT SPLIT_PART(SPLIT_PART(sighting_report, 'Shape', 1), 'Location: ', 2) AS location
	FROM ufo
) AS A
ORDER BY 1;

Occurred, Entered, Reported, Posted, Shape, Duration 텍스트 변환

• SPLIT_PART() 함수를 통해 각각의 컬럼에 해당하는 텍스트를 추출한다.
• 위 내용을 서브쿼리로 사용한다.
• 문자열의 길이가 8개 미만일 경우 NULL, 아닐 경우 날짜 데이터 변환한다.
• location은 close to와 outside of 문자를 near로 변경한다.
• shape는 첫 문자를 대문자로 변환한다.
• duration은 양쪽의 공백을 제거한다.

SET datestyle = 'mdy';
SELECT
	(CASE
	 	WHEN occurred = '' THEN NULL
	 	WHEN LENGTH(occurred) < 8 THEN NULL
	 ELSE occurred::TIMESTAMP
	END) AS occurred,
	entered_as,
	(CASE
	 	WHEN LENGTH(reported) < 8 THEN NULL
	 	ELSE reported::TIMESTAMP
	END) AS reported,
	(CASE
	 	WHEN posted = '' THEN NULL
	 	ELSE posted::DATE
	END) AS posted,
	REPLACE(REPLACE(location, 'close to', 'near'), 'outside of', 'near') AS location,
	INITCAP(shape) AS shape,
	TRIM(duration) AS duration
FROM (
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
) AS A
ORDER BY 1;