SUBSTRING(), REPLACE(), POSITION() and COALESE()

SQL에는 여러 다양한 함수가 있습니다.
이번 포스팅에서는 문자열 데이터를 다루는데 유용한 함수를 소개하고자 합니다.

모든 작업은

Postgresql을 사용해 작업했습니다.

 

SUBSTRING(), REPLACE(), POSITION() and COALESE()
위 4가지 함수입니다.

1. SUBSTRING

SUBSTRING은 특정위치에 있는 문자가 무엇인지 알기위해 사용합니다.

SUBSTRING을 활용해 동일한 출력을 가진 다른 표현방법을 소개합니다.

SELECT SUBSTRING('THIS IS TEST SENTENCE', 1,7);

SELECT SUBSTRING('THIS IS TEST SENTENCE' FROM 1 FOR 7);

위 2개의 쿼리를 실행하면 THIS IS 가 출력됩니다.
SUBSTRING('문자열', 시작위치, 출력할 문자열 크기)

 

2.REPLACE

 

REPLACE는 특정컬럼 내 셀값을 다른 값으로 바꿀 때 사용합니다.

아래와 같은 테이블이 있다고 가정해보겠습니다.

테이블 명 : subject

 

id	subject
1	math
2	english
3	science
4	korean
5	english
6	society

 subject컬럼 내 있는 english 값을 spanish로 바꾸려고 합니다.

그럼 아래와 같은 쿼리문을 작성하면 됩니다.

SELECT id, REPLACE(subject, 'english', 'spanish')
FROM subject

REPLACE(바꾸고자 하는 값이 속한 열, '바꾸기 전의 값','바꿀 값') 으로 입력하는 순서입니다

 

3. POSITION()

 

POSITION()은 SUBSTRING()과는 반대로 특정문자열의 위치를 알고 싶을 때 사용합니다.
아래와 같은 테이블이 있다고 가정하겠습니다.

테이블명 : people

 

name	email
김철수	chulsoo@naver.com
오나영	na.na@happything.com
김갑수
진나비	butterfly@daum.net

우선 우리는 mail에서 @가 몇번째에 위치하는지 알고 싶습니다.
POSITION을 사용하면 전체문자열에서 특정 문자가 몇번째에 위치하는지 알 수 있습니다.

SELECT POSITION('@' IN email)
FROM people

위의 쿼리를 이용하면 아래와 같은 결과물이 나올겁니다.

 

 

8

6

null

10

더 나아가
사람들이 어떤 이메일 계정을 쓰고 싶은지 알고 싶습니다. 하지만 도메인계정의 길이가 제각각입니다.
우리는 @가 몇번째에 위치하는지 알고 있기 때문에 앞서배운 SUBSTRING을 활용해 볼 수 있을 것 같습니다.

SELECT SUBSTRING(email, POSITION('@' IN email) + 1)
FROM people

쿼리문을 설명하면
POSITION은 해당 문자열이 몇번째에 위치해있는지를 알 수 있고 SUBSTRING 함수를 통해 위치해 있는 자리 + 1 을 함으로써 @이후 나오는 문자열을 모두 출력하게 됩니다.
결과는 아래와 같이 값이 나올 것 입니다.

 

 

 

naver.com

happything.com

null

daum.net

 

 

4.COALESCE()

coalesce() 함수는 빈 값을 특정 값으로 대체하기 위해 사용합니다.
위의 people 데이블에 김갑수는 email 데이터를 가지고 있지 않습니다. 하지만 쿼리문에서는 null로 표현되어 'null'이라는 데이터가 있는 것처럼 보입니다.
이럴 때 이러한 값을 깔끔하게 해주기 위해 coalesce함수를 사용합니다.

COALESCE(열 이름, '대체할 표현')

SELECT COALESCE(email,'') 
FROM People

 

위 쿼리문을 쓰면 아래와 같은 결과가 나오게 됩니다.

 

 

 

chulsoo@naver.com

na.na@happything.com

butterfly@daum.net

 

coalesce()함수는 group by()와 같은 집계함수와 사용하면 더욱 유용하게 사용할 수 있을 것으로 보입니다.