SQL 문자함수 - ctrl + F를 활용하여 원하는 함수를 찾아보세요! 종류
|
1. LOWER(대상) & UPPER(대상)
LOWER 함수와 UPPER 함수는 문자열을 각각 소문자와 대문자로 만들 때 사용됩니다.
# 사용예시
SELECT LOWER('ABC') FROM DUAL; -- 결과 : abc
SELECT UPPER('abc') FROM DUAL; -- 결과 : ABC
LOWER와 UPPER는 검색을 할 때 활용 될 수 있습니다. 예를들어 데이터 베이스에는 APPLE로 저장되어 있는데 사용자가 apple, Apple 등으로 검색했을 때 해당 검색어와 대소문자가 달라도 값을 비교할 수 있도록 Java에서도 toUpperCase를 해주고 DB쿼리를 조회할 때에도 UPPER를 활용하여 둘 다 대문자로 비교할 수 있도록 해줍니다.
2. SUBSTR(대상, m, n)
SUBSTR은 문자열을 잘라서 원하는 부분만 활용하고 싶을 때 사용합니다. 대상 문자열 중 m번째 위치에서 n개의 문자열을 추출합니다. 여기서 n을 생략하면 m번째 위치에서 끝까지 문자열을 가져옵니다.
# 사용예시
SELECT SUBSTRB('ABCDEFGH',2,3) FROM DUAL; -- BCD 출력
SELECT SUBSTRB('ABCDEFGH',2) FROM DUAL; -- BCDEFGH 출력
SUBSTR의 활용도는 굉장히 높습니다. 예를 들어 회원가입 페이지를 만들 때 데이터베이스에는 주민번호와 성별을 두개 다 입력받을 필요가 없습니다. 주민번호 뒷번호의 첫 번째 자리를 가져와서 1,3인지 혹인 2,4인지에 따라 남성인지 여성인지 확인이 가능합니다. 그렇다면 주민번호 컬럼을 SUBSTR을 활용하여 뒷자리의 첫 번째 값을 가져올 수 있겠죠?
3. INSTR(대상, 찾을문자열, n, m)
INSTR은 대상에서 찾을 문자열의 위치를 반환합니다. (n과 m은 생략가능)
- 첫 번째 매개변수: 대상 문자열
- 두 번째 매개변수: 찾을 문자 또는 문자열
- 세 번째 매개변수 (선택적): 시작 검색 위치 (기본값은 1)
- 네 번째 매개변수 (선택적): 찾는 문자 또는 문자열이 몇 번째로 나오는지 지정하는 인덱스
# 사용예시
SELECT INSTR('A@B@C@', '@') FROM DUAL; -- 결과 : 2
SELECT INSTR('A@B@C@', '@', 3) FROM DUAL; -- 결과 : 4
SELECT INSTR('A@B@C@', '@', 3, 2) FROM DUAL; -- 결과 : 6
첫 번째 쿼리는 '@'가 처음으로 등장하는 위치를 리턴합니다.
두 번째 쿼리는 3번째 위치 이후로 '@'가 처음 등장하는 위치를 반환합니다.
세 번째 쿼리는 3번째 위치 이후로 '@'가 2번째 등장하는 위치를 반환합니다.
INSTR은 주로 데이터 가공이나 검색, 필터링 등에 자주 사용됩니다. 예를 들어 이메일 주소에서 앞부분 아이디만 가져오고 싶다면 위에서 나온 SUBSTR과 함께 활용하여 다음과 같은 쿼리를 작성할 수 있습니다.
SELECT
email_address,
SUBSTR(email_address, 1, INSTR(email_address, '@') - 1) AS username
FROM
users;
4. LTRIM(대상, 삭제문자열) & RTRIM(대상, 삭제문자열) & TRIM(대상)
LTRIM과 RTRIM은 문자열 중 특정 문자열을 각각 왼쪽과 오른쪽에서 삭제할 때 사용합니다. 만약 두 번째 파라미터인 삭제문자열을 생략한다면 공백을 찾아서 지워줍니다. TRIM은 문자열 중 특정 문자열을 양쪽에서 삭제해 주는데 Oracle에서는 공백만 삭제 가능합니다.
# 사용예시
SELECT LTRIM('AABABAA', 'A') FROM DUAL; -- 결과 : BABAA
SELECT RTRIM('AABABAA', 'A') FROM DUAL; -- 결과 : AABAB
SELECT TRIM(' ABCED ') FROM DUAL; -- 결과 : ABCDE
위 쿼리를 보면 LTRIM을 사용했을 땐 'AABABAA' 중 앞에 있는 A가 전부 삭제되었고 RTRIM은 반대로 뒤쪽에 있는 A가 전부 삭제되었습니다. 마지막으로 'ABCDE'라는 문자열의 양쪽에 공백이 들어가 있는데 TRIM을 통해 양쪽 공백을 전부 삭제한 것을 확인 할 수 있습니다. TRIM은 주로 사용자가 입력한 값에 불필요한 공백이나 특수기호가 들어갔을 때 삭제를 해주는 등 데이터 검색 및 필터링이나 데이터 저장 등에 많이 활용됩니다.
5. LPAD(대상, n, 문자열) & RPAD(대상, n, 문자열)
LPAD와 RPAD는 대상의 각각 왼쪽, 오른쪽에 문자열을 추가하여 총 n길이의 문자열로 리턴해주는 함수입니다.
- 대상 : 왼쪽으로 채울 대상 문자열입니다.
- n : 최종 문자열의 전체 길이를 지정합니다. 이 길이보다 대상 문자열의 길이가 짧을 경우, 왼쪽(오른쪽)에 문자열을 추가하여 채웁니다.
- 문자열 : 대상 문자열을 채울 문자열입니다. 이 문자열이 왼쪽에 대상 문자열을 채우는 데 사용됩니다.
# 사용예시
SELECT LPAD('ABC', 5, '*') FROM DUAL; -- 결과 : **ABC
SELECT RPAD('ABC', 5, '*') FROM DUAL; -- 결과 : ABC**
대상 문자열은 'ABC'로 3글자인데 가운데 파라미터의 5인 숫자를 보면 총길이를 5자까지 채우겠다고 선언했습니다. 그리고 마지막 파라미터에 나머지 2글자는 '*'로 채우기로 했으므로 위와 같은 결과 값이 나옵니다. 아래 사진처럼 게시글 내용이나 구매내역 등 긴 글을 축약할 때 많이 사용합니다.
6. CONCAT(대상 1, 대상 2)
CONCAT 함수는 두 개의 문자열을 결합할 때 사용합니다.
# 사용예시
SELECT CONCAT('A', 'B') FROM DUAL; -- 결과 : AB
CONCAT 함수는 성과 이름을 다른 컬럼으로 저장했을 때 풀네임 값을 활용하기 위해 사용될 수 있습니다.
7. LENGTH(대상)
LENGTH함수는 문자열의 길이를 조회할 때 사용합니다.
# 사용예시
SELECT LENGTH('ABCD') FROM DUAL; -- 결과 : 4
8. REPLACE(대상, 찾을문자열, 바꿀문자열)
REPLACE 함수는 문자열 치환 및 삭제 시 사용합니다.
- 대상 : 변환을 적용할 원본 문자열입니다. 문자열 또는 문자열을 포함하는 열을 지정할 수 있습니다.
- 찾을문자열 : 원본 문자열에서 찾고자 하는 부분 문자열입니다. 이 부분 문자열이 발견되면 세 번째 매개변수인 바꿀문자열로 대체됩니다.
- 바꿀문자열 : 원본 문자열 내에서 찾을문자열을 대체할 문자열입니다. 찾을문자열이 발견될 때마다 이 문자열로 대체됩니다.
# 사용예시
SELECT REPLACE('ABBA', 'AB', 'ab') FROM DUAL; -- 결과 : abBA
SELECT REPLACE('ABBA', 'AB') FROM DUAL; -- 결과 : BA
SELECT REPLACE('ABBA', 'AB', '') FROM DUAL; -- 결과 : BA
두 번째 쿼리와 세 번째 쿼리는 3번째 파라미터를 생략하거나 빈 문자열을 넘겼기 때문에 찾으려고 했던 'AB'가 완전히 삭제된 것을 확인할 수 있습니다. 아래와 같이 전화번호나 날짜 포맷팅 변경 시 주로 사용될 수 있습니다.
SELECT REPLACE('123-456-7890', '-', '') AS phone_number; -- 결과 : 1234567890
9. TRANSLATE(대상, 찾을문자열, 바꿀문자열)
TRANSLATE 함수는 글자를 1대1로 치환할 때 사용합니다. 사용방법은 바로 위에서 설명한 REPLACE와 똑같지만 리턴값에 차이점이 있습니다.
- 대상 : 변환을 적용할 원본 문자열입니다. 문자열 또는 문자열을 포함하는 열을 지정할 수 있습니다.
- 찾을문자열 : 원본 문자열에서 찾고자 하는 문자 집합입니다. 이 문자 집합의 각 문자는 바꿀문자열의 각 문자와 일대일 대응으로 변환됩니다.
- 바꿀문자열 : 원본 문자열 내에서 찾을문자열의 각 문자를 대체할 문자 집합입니다. 찾을문자열의 각 문자에 해당하는 문자로 대체됩니다.
# 사용예시
SELECT TRANSLATE('ABBA', 'AB', 'ab') FROM DUAL; -- 결과 : abba
SELECT TRANSLATE('ABBA', 'AB', '') FROM DUAL; -- 결과 : (null)
차이점을 보셨나요? REPLACE 였을 때에는 AB의 순서가 정확히 일치하는 것만 치환되었다면 TRANSLATE는 글자를 1대 1로 치환하여 모든 글자가 바뀐 것을 볼 수 있습니다. 또한 마지막 파라미터에 빈문자열을 입력했을 때 삭제가 아닌 (null)을 리턴합니다. 상황에 따라 REPLACE를 사용할지 TRANSLATE를 사용할지 잘 고려해야 할 것 같습니다.
'DB' 카테고리의 다른 글
[DB] 예시와 함께 보는 SQL 날짜 함수 총 정리! (0) | 2024.06.09 |
---|---|
[DB] 예시와 함께 보는 SQL 숫자 함수 총 정리! (2) | 2024.06.08 |
[DB] 트랜잭션, 비전공자도 한방에 이해시켜버림! (0) | 2024.06.06 |
[DB] 정규화 이것보다는 쉽게 설명 못합니다! (0) | 2024.06.05 |
[DB] 데이터 모델링 완벽 이해하기!(엔터티, 속성, 관계 / 개념, 논리, 물리 모델링) (0) | 2024.06.04 |