ORACLE | SQL 문자함수(INSTR, REGEXP_REPLACE, TRIM)

SQL이란,

 관계형 데이터베이스 관리 시스템에서

 자료의 검색 및 관리 등을 위해 설계된 프로그래밍 언어입니다.

출처: 위키백과

이러한 SQL 에는 다양한 함수가 존재하는데요.

이러한 함수는 크게 '단일 행 함수'와 '복수 행 함수'로 나뉠 수 있습니다.

오늘은 단일 행 함수의 속하는 다양한 함수 중 문자함수에 대해 알아보겠습니다.

---

데이터 검색을 할 때 함수가 필요한 이유는 무엇일까요?

그 이유는!

함수를 이용하면 더 다양하게 데이터를 검색할 수 있기 때문입니다.

문제를 통해 이 사실을 확인합시다.

현재 DB에 있는 EMAIL의 형식을 아래와 같이 가정합니다.

'아이디 @ naver.com' '아이디 @ daum.com' '아이디 @ gmail.com'  .  .  .  .

과연 함수 없이 다음 문제를 풀 수 있을까요?

문제1. 이메일에서 @가 몇 번째 철자에 있는지 알려주세요!

문제2. 내 이메일 주소를 다른 사람이 알 수 없도록 다른 철자로 변경해주세요!

문제3. 이메일을 입력하다 모르고 처음에 공백을 넣어서 찾을 수가 없습니다. 해결해주세요.

이때 사용하는 함수를 각각 소개하겠습니다.

문제1. 이메일에서 @가 몇 번째 철자에 있는지 알려주세요!

SELECT instr(email, '@')

FROM 테이블명;

참고: @ 을 '@' 이렇게 싱글 쿼테이션으로 감싸는 이유!

SQL에서 싱글 쿼테이션( ' )은 문자를 의미하며

숫자와는 다르게 문자와 날짜는 양쪽에 싱글 쿼테이션 마크가 있어야 합니다.

이 때 사용하는 함수가 바로 INSTR 함수입니다.

instr함수는 특정 철자가 단어에서 몇 번째 철자인지 확인하는 함수죠!

(단, 처음 나오는 특정 철자의 자리를 알려준답니다!)

형식: 

INSTR (string, substring [, position ] )

'string'

 입력할 문자열을 지정하는 부분입니다.

'substring'

 위치를 반환할 문자열을 지정하는 것입니다.

'position'

 탐색을 시작할 string의 위치를 나타내며, 문자 재수 단위로 지정됩니다.

 이 인자가 생략되면 기본값인 1이 적용됩니다. 즉, 선택사항이죠

반환 값은 INT 형식으로 숫자가 return됩니다.

출처: CUBRID

문제2. 내 이메일 주소를 다른 사람이 알 수 없도록 다른 철자로 변경해주세요!

SELECT regexp_replace(email, '[a-z]', '#')

FROM 테이블명;

또는

SELECT regexp_replace(email, '[0-9]', '*')

FROM 테이블명;

등 다양한 방식으로 이 문제를 풀 수 있습니다.

참고) '[a -z]'와 '[0-9]'는 정규표현식입니다.

알아두면 좋습니다. 필요할 때마다 찾아보고 익히도록해요~ 

물론 더 좋은 보안 시스템을 이용할 수도 있죠.

하지만,

제가 여기서 강조하고 싶은 부분은 바로 'regexp_replace'함수 입니다.

regexp_replace 함수는 여러개의 철자를 한번에 다른 철자로 변경하는 함수입니다.

형식:

REGEXP_RPLACE ( source_string, pattern [ , replace_string [ , position] ] )

'source_string'

 열 이름 같이 검색할 문자열 표현식입니다.

'pattern'

 SQL 표준 정규 표현식 패턴을 나타내는 문자열 리터럴입니다.

'replace_string'

 발견되는 패턴을 각각 변형할 문자열 표현식(열 이름 등) 입니다. 기본값은 빈 문자열 입니다.

'position'

source_string 내에서 검색을 시작할 위치를 나타내는 양의 정수입니다. 이 위치는 바이트가 아닌 문자 수를 기준으로 하기 때문에 멀티바이트 문자도 단일 문자로 계산됩니다. 기본 값은 1입니다.

반환 값은 VARCHAR 형식으로 문자가 return 됩니다.

출처: AWS

추가) 만약 특정 철자만 다른 철자로 변경하고 싶다면 어떻게 해야할까요?

바로 replace 함수를 이용하면 됩니다.

비교해서 알아두면 좋습니다.

replace 함수와 regexp_replace 함수의 차이를 찾아보도록해요~

여기 (엄청 많은 포스팅이 있습니다!)

마지막 문제!!

문제3. 이메일을 입력하다 모르고 처음에 공백을 넣어서 찾을 수가 없습니다. 해결해주세요.

SELECT email

FROM 테이블명

WHERE trim(email) = 'my email'

또는

SELECT email

FROM 테이블명

WHERE rtrim(email) = 'my email'

여기서 소개할 함수는 'TRIM' 함수 입니다.

TRIM함수는 특정 철자를 잘라내는 함수 입니다.

TRIM함수의 종류와 기능은 아래와 같습니다.

TRIM()	--> 양쪽 공백을 잘라내겠다
RTRIM()	--> 오른쪽 공백을 잘라내겠다.
LTRIM()	--> 왼쪽 공백을 잘라내겠다.

형식:

TRIM( [ BOTH ] [ 'characters' FROM ] string] )

'BOTH'

LEADING : 왼쪽에서 제거  

TRAILING : 오른쪽에서 제거

BOTH : 양쪽에서 제거

특별히 설정해주는 값이 없으면 기본값은 BOTH 입니다.

'characters'

문자열에서 잘라낼 문자입니다. 이 파라미터를 생략하면 공백이 잘립니다.

'string'

자르기 대상이 되는 문자열입니다.

반환 값은 varchar 또는 char 문자열을 return 합니다.

출처: aws, 좋은 블로그

---

우와! 드디어 끝!!!

여기까지 보신 분들 오늘 남은 시간도  화이팅 합시다!

이 글이 도움되셨다면 아래 공감 꾹~