[SQL 개념 정리 4] 변환 쿼리

변환 쿼리

---

문자열 변환 함수

문자열 변환 함수는 텍스트 데이터를 다룰 때 사용되며, 특정 패턴으로 문자열을 변환하거나 길이를 조정하는 데 유용합니다.

 

주요 문자열 변환 함수

• UPPER: 문자열을 대문자로 변환합니다.

SELECT UPPER(name) AS upper_name FROM students;

 

• LOWER: 문자열을 소문자로 변환합니다.

SELECT LOWER(name) AS lower_name FROM students;

 

• CHR (또는 CHAR): 입력되는 ASCII 코드값에 대응하는 문자를 반환합니다.

SELECT CHR(97) FROM DUAL;
-- a 출력

 

• CONCAT: 문자열을 연결합니다.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM students;

 

• SUBSTRING (또는 SUBSTR): 문자열의 일부를 추출합니다.

SELECT SUBSTRING(name, 1, 3) AS name_prefix FROM students;

이 쿼리는 name의 첫 번째 문자부터 3개의 문자 추출하는 쿼리입니다.

 

• TRIM: 문자열의 앞뒤 공백 또는 지정된 문자열을 제거합니다.
  • LEADING, TRAILING, BOTH 옵션을 활용하여 원하는 부분에서의 지정된 문자열 제거 가능 (생략될 경우 기본값은 BOTH).

SELECT TRIM(name) AS trimmed_name FROM students;

 

• LTRIM: 문자열의 왼쪽 끝에서 공백 또는 지정된 문자열을 제거합니다.

SELECT LTRIM(name) AS left_trimmed_name FROM students;

 

• RTRIM: 문자열의 오른쪽 끝에서 공백 또는 지정된 문자열을 제거합니다.

SELECT RTRIM(name) AS right_trimmed_name FROM students;

 

• LENGTH: 문자열의 길이를 반환합니다.

SELECT LENGTH(name) AS name_length FROM students;

 

• REPLACE: 입력된 문자열에서 특정 문자열을 찾아 다른 문자열로 대체하여 반환합니다.

SELECT REPLACE('Good Morning', 'Morning', 'Afternoon') FROM DUAL;
-- Good Afternoon이 출력됨.

SELECT REPLACE('Good Morning', 'Good') FROM DUAL;
-- Good이 삭제되어 Morning만 출력됨.

---

날짜 변환 함수

날짜 변환 함수는 날짜 데이터를 처리할 때 사용됩니다. SQL에서는 날짜 데이터를 조작하는 여러 함수를 제공하며, 특정 형식으로 날짜를 변환하거나 연산을 수행할 수 있습니다.

 

주요 날짜 변환 함수

• DATE_FORMAT: 날짜를 특정 형식으로 변환합니다 (MySQL에서 사용).

SELECT DATE_FORMAT(birth_date, '%Y-%m-%d') AS formatted_date FROM students;

 

• YEAR, MONTH, DAY: 날짜에서 연도, 월, 일을 추출합니다.

SELECT YEAR(birth_date) AS birth_year FROM students;
SELECT MONTH(birth_month) AS birth_month FROM students;
SELECT DAY(birth_day) AS birth_day FROM students;

 

• DATEDIFF: 두 날짜 간의 차이를 계산합니다.

SELECT DATEDIFF(NOW(), birth_date) AS days_since_birth FROM students;

 

• DATE_ADD: 날짜에 특정 간격을 추가합니다.

SELECT DATE_ADD(start_date, INTERVAL 7 DAY) AS one_week_later FROM events;

 

• CURDATE() / NOW() (혹은 SYSDATE): 현재 날짜 및 시간을 반환합니다.

SELECT CURDATE() AS today_date;

 

---

수치 변환 함수

수치 변환 함수는 숫자 데이터를 조작할 때 사용됩니다. 주로 값을 반올림하거나 특정 형식으로 변환하는 데 유용합니다.

 

주요 수치 변환 함수

• ROUND: 숫자를 반올림합니다.

SELECT ROUND(salary, 2) AS rounded_salary FROM employees;

 

• CEIL 또는 CEILING: 숫자를 올림합니다.

SELECT CEIL(salary) AS ceiling_salary FROM employees;

 

• TRUNC: 숫자를 반올림하지 않고 특정 자릿수에서 버림한 후 그 결과를 반환합니다.

SELECT TRUNC(salary, 1) AS floor_salary FROM employees;

 

• FLOOR: 값보다 작거나 같은 최대의 정수를 반환합니다.(버림)

SELECT FLOOR(salary) AS floor_salary FROM employees;

 

• SIGN: 입력된 값이 양수이면 1, 음수이면 -1, 0이면 0을 반환합니다.

SELECT SIGN(2.3) FROM DUAL;
-- 1 반환

 

• ABS: 절대값을 반환합니다.

SELECT ABS(balance) AS absolute_balance FROM accounts;

 

• POWER: 거듭제곱을 계산합니다.

SELECT POWER(salary, 2) AS salary_square FROM employees;

 

• MOD: 나머지를 계산합니다.

SELECT MOD(salary, 1000) AS remainder FROM employees;

 

---

형 변환 함수 (데이터 타입 변환)

SQL에서는 데이터 타입을 변환할 수 있는 함수가 제공됩니다. 특히 CAST와 CONVERT는 데이터 타입을 명시적으로 변경할 때 유용합니다.

 

주요 형 변환 함수

• TO_NUMBER: 데이터 타입을 숫자형으로 변환한다.

SELECT TO_NUMBER('10') AS user_id FROM employees;

 

• TO_CHAR: 데이터 타입을 문자열형으로 변환한다.

SELECT TO_CHAR(10) AS user_id FROM employees;

 

• TO_DATE: 데이터 타입을 날짜형으로 변환한다.

SELECT TO_DATE('20241113', 'YYYYMMDD') AS TDOAY FROM employees;

 

• CAST: 특정 데이터 타입으로 변환합니다.

SELECT CAST(salary AS CHAR) AS salary_char FROM employees;

 

• CONVERT: 특정 데이터 타입으로 변환합니다 (데이터베이스에 따라 지원되는 형식이 다를 수 있음).

SELECT CONVERT(salary, CHAR) AS salary_char FROM employees

 

---

CASE 문: 조건에 따른 값 변환

CASE 문은 조건에 따라 값을 변환하는 데 사용되며, 데이터를 특정 기준에 따라 범주화할 때 유용합니다.

 

예시

SELECT name,
       CASE
           WHEN age < 18 THEN 'Minor'
           WHEN age BETWEEN 18 AND 64 THEN 'Adult'
           ELSE 'Senior'
       END AS age_group
FROM students;

• 이 쿼리는 age 값을 기준으로 age_group 열을 생성하여, 나이에 따라 Minor, Adult, Senior로 분류합니다.

---

NULL 값 변환

• NVL (또는 ISNULL): 첫 번째 인자가 NULL이 아니면 첫 번째 인자를 그대로 반환하고 Null이면 두 번째 인자를 반환합니다.
  • 첫 번째와 두 번째 인자의 데이터 타입은 같아야 함.

SELECT Student_ID, name, NVL(count, 0) AS count FROM students;

이 쿼리는 count 컬럼의 값이 NULL인 것을 대상으로 0을로 치환하여 반환합니다.

• NULLIF: 입력된 두 인자가 같으면 NULL을 반환하고 다르면 첫번째 인자를 반환합니다.

SELECT Student_ID, name, NULLIF(count, 15) FROM students;

이 쿼리는 count 컬럼의 값이 15인 것을 NULL로 치환하여 반환합니다. 

• IFNULL (또는 COALESCE): NULL 값을 다른 값으로 변환할 때 사용합니다.
  
  예시

SELECT name, IFNULL(phone, 'No Phone') AS contact_info FROM students;

phone 열이 NULL이면, contact_info 열에 'No Phone'이 표시됩니다.

SELECT name, COALESCE(phone, 'No Phone') AS contact_info FROM students;

COALESCE도 NULL을 특정 값으로 변환하는 데 사용되며, 다중 조건을 설정할 수 있습니다.

 

---

SQL의 다양한 변환 함수는 데이터를 더욱 유연하고 효율적으로 다룰 수 있게 해줍니다. 텍스트와 날짜, 숫자, 데이터 타입 변환 함수뿐만 아니라 조건에 따른 CASE 문과 NULL 값 처리 기능을 통해, 우리는 복잡한 데이터 처리와 분석 요구를 더 쉽게 충족할 수 있습니다. 이런 변환 함수들을 적절히 활용하면, 데이터를 보다 직관적이고 의미 있게 가공하여 더욱 풍부한 인사이트를 도출할 수 있습니다.