오라클 띄어쓰기 넣기 - olakeul ttuieosseugi neohgi

이번 포스팅에서는 오라클에서 문자열을 자를때 사용하는 SUBSTR()와 구분자로 자를때 구분자만큼의 위치를 리턴하는 INSTR() 함수에 대해 포스팅하겠습니다.

문자열 자르기 SUBSTR( ) 함수

--사용법(문자열, 시작위치, 길이)
SUBSTR(CHAR,POS,LEN)

--예제
WITH EXAMPLE AS(
SELECT '동해물과 백두산이 마르고 닳도록' AS STR FROM DUAL
)

SELECT
SUBSTR(STR,1,4), --STR의 1번째 글자부터 4개 자르기
SUBSTR(STR,5,5), --STR의 5번째 글자부터 5개 자르기
SUBSTR(STR,10), --STR의 10번째 글자부터 끝까지 자르기
SUBSTR(STR,-7,3), --STR의 뒤에서 3번째 글자부터 3글자 자르기
SUBSTR(STR,-3), --STR의 뒤에서 3번째 글자부터 끝까지 자르기
FROM
EXAMPLE

오라클은 SQL처럼 LEFT, RIGHT라는 함수가 없어 문자열 자르기를 SUBSTR함수로만 잘라야합니다. SUBSTR은 문자열인 CHAR의 POS번째 문자부터 LEN길이만큼 잘라낸 결과를 리턴하는 함수입니다. POS값이 0이오면 디폴트값인 1, 즉 문자열의 첫번째를 가리키고 음수가 오면 문자열의 뒤에서부터 시작하여 카운트한 숫자를 의미합니다. LEN값을 생략할수도 있는데 LEN값을 생략하게 되면 POS번째 문자부터 나머지 모두를 반환합니다. 

구분자로 문자열 자르기 / 문자열 위치 찾기 INSTR( ) 함수

--사용법(문자열, 타겟문자열, 시작위치, 횟수)
INSTR(STR,SUBSTR,POS,OCCUR)

--예제
WITH EXAMPLE AS(
SELECT '동해물과 백두산이 마르고 닳도록' AS STR FROM DUAL
)

SELECT
INSTR(STR, ' '), --' ' STR에서 띄어쓰기 있는 문자 위치 찾기
INSTR(STR, ' ', 7), --STR의 7째 문자부터 띄어쓰기가 있는 문자 위치 찾기
INSTR(STR, ' ', 7,2), --STR의 7째 문자부터 2번째 띄어쓰기가 있는 문자 찾기
SUBSTR(STR, 0, INSTR(STR, ' ', 1, 2)) --띄어쓰기 2번째 문자열까지 문자열 자르기
FROM
EXAMPLE

프로그래밍을 하다보면 문자열을 길이로 자르고싶은게 아니라 특정 구분자로 잘라야할때가 있습니다. 주로 전화번호나 주소같은경우가 대표적이죠. (위와같이 특정 문자뿐만아니라 띄어쓰기(공백)도 가능합니다.) 이때는 INSTR이라는 함수로 먼저 구분자까지의 문자를 찾고 SUBSTR을 사용하여 문자열을 자르면 됩니다. INSTR함수는 STR문자열에서 SUBSTR과 일치하는 위치를 반환하는데 POS는 시작위치로 명시해주지않으면 1로 들어갑니다. OCCUR은 횟수를 의미하는데 마찬가지로 명시해주지 않으면 1을 반환하여 위의 예제와 같은 결과가 나오게 됩니다.

[데이터베이스 DBMS SQL 완전정복 포스팅 목차]

안녕하세요~~ 오늘도 SQL구문 하나를 들고 왔습니다.

꼭 알아야 할 필수 구문 중 하나이죠!ㅎㅎ

EMPLOYEES.csv

0.01MB / 0.01MB

데이터는 이전에 같이 다운로드 받았던 EMPLOYEES 테이블을 예시로 사용하였습니다.

CONCAT 함수란? CONCAT 문법

CONCAT은 CONCATENATION의 약자입니다. concatenation 단어 자체가 '문자열의 연결'이라는 뜻이랍니다.ㅎㅎ

넹. 문자열을 연결하고 싶을 때 CONCAT을 사용합니다.

문자열1과 문자열2를 연결하고 싶을 때에는 CONCAT(문자열1, 문자열2)를 해주면 됩니다.

SIMPLE TEST

SELECT CONCAT('양햄찌 ', '블로그!') 문자열결과
FROM  DUAL;

"양햄찌 " + "블로그!" = "양햄찌 블로그!" 이렇게 되겠죠?ㅎㅎ

결과가 잘 뽑힌 것을 확인할 수 있습니다.

문자열을 이어 붙이고 싶을 때 CONCAT 함수 사용하자 

데이터베이스의 가장 기본 원칙 중 하나는 컬럼당 하나의 특성만 저장해야 한다는 겁니다.

그래서 여러 값을 하나의 속성에 때려넣지 않아요. 그런데!! 사용자 입장에서는 이러한 값들을 연결해서 확인하고 싶을 상황이 많죠.

한 예로 EMPLOYEES 테이블에 '성'과 '이름'이 각각 다른 컬럼에 저장되어 있어요. 

하지만 어떤 사람이 '뽀로로'라는 사람이 혹시 이 회사에서 근무하나요? 라고 물어볼 수 있겠죠!

그럼 우리는 성+이름 합친 값을 기준으로 데이터를 찾아야해요. 

그래서 요렇게 성과 이름을 붙여줬어요.

흠 잘 뽑히긴 하는데 FIRST_NAME과 LAST_NAME 사이에 띄어쓰기가 없으니까 어디까지가 성이고 어디까지가 이름인지 헷갈리는군요!

여러 개의 문자열을 한꺼번에 붙이려면? 중첩 CONCAT?

SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) 이름, 
		PHONE_NUMBER 전화번호
FROM EMPLOYEES ;

사이에 띄어쓰기를 넣어주고 싶어서 요렇게 작성해봤습니다. 

앗..! 그랬더니 '인수의 개수가 부적합합니다' 에러가 남을 확인할 수 있어요. [ORACLE 사용]

그런데 MYSQL에서는 또 정상적으로 실행이 돼요.

왜 그러냐!

SQL Server, MySQL, 오라클, PostgreSQL 등등 모두 CONCAT함수를 지원하는데, 어떤 DBMS냐에 따라서 매개변수를 두 개만 받기도 하고 여러개를 허용해주기도 하고 다르답니다. 오라클은 매개변수를 두 개만 받기 때문에 여러 문자를 연결하려면 중첩해서 CONCAT을 사용해줘야 합니다. 

SELECT CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) 이름, 
		PHONE_NUMBER 전화번호
FROM EMPLOYEES ;

요렇게!! 붙여야할게 많아지면 좀 수식이 복잡해지겠죠;

띄어쓰기가 잘 되었습니다.

오라클에서 문자열을 이어주는 ||연산자?  MYSQL에서는 참을 판별!

[ORACLE]

문자열을 연결시켜주는 도구로는 또 '||'연산자가 있습니다

CONCAT같은 경우 직관적이지도 않고 저렇게 중첩되면 작성하는 사람도 헷갈려서 실무에서는 ||를 더 자주 써요.

SELECT FIRST_NAME || ' ' || LAST_NAME 이름, 
		PHONE_NUMBER 전화번호
FROM EMPLOYEES ;

게다가 짧고, 간단하고, 포맷이 동일해서 더 간편하게 느껴집니다.

마찬가지로 FIRST_NAME과 LAST_NAME 사이에 띄어쓰기가 잘 들어간 것을 확인할 수 있어요.

[MYSQL]

이를 동일하게 MYSQL로 실행해봤습니다.

그런데 이를 MYSQL에서 수행하면 결과가 다르게 나옵니다. 

왜냐 오라클에서 ||와 MYSQL에서 || 연산자가 지원하는게 각기 다르기 때문인데요.

오라클에서 ||가 문자열 이어붙이기였다면 MYSQL에서는 우리가 아는 논리연산자 OR를 의미하기 때문입니다.

'A || B' 하면 A나 B 둘 중 하나가 참이면 모두 참 이라는 의미를 가진 연산자가 됩니다.

만약 이어붙이려는 타입이 문자열이 아니면 어떻게 될까?

문자열끼리 이어붙이면 된다는건 알겠는데 문자열이 아닌 데이터 유형일 경우 CONCAT를 사용할 수 있을까요?

테이블을 보시면 emp_no가 int 타입으로, gender은 ENUM 타입으로 되어있는 것을 알 수 있습니다. 

이 둘의 타입을 문자열과 같이 이어붙여봤어요. 잘 이어진 것을 확인할 수 있습니다.

[NULL테스트]

SELECT CONCAT(CONCAT(1234, NULL),'hello') 문자열결과
FROM  DUAL;

이번에는 NULL을 넣어서 테스트 해볼까요?

MYSQL에서는 CONCAT에 NULL이 포함되어 있으면 결과로 NULL을 리턴합니다. 

반면에 오라클의 경우 NULL을 빈 문자열로 간주하고 이어붙인 결과를 리턴하는 것을 확인할 수 있어요.

즉 오라클에서 CONCAT 함수는 모든 인수를 묵시적으로 문자열 형식으로 변환시킨 다음에 연결이 됩니다. 따라서 NULL인 데이터도 빈 문자열로 변환되어서 연결이 돼요. ||연산자도 마찬가지예요. 그런데 MYSQL에서는 NULL은 빈 문자열로 변환시키지 않고 NULL이 포함되었을 경우 결과를 NLL로 리턴한다는 것을 알 수 있습니다.

SELECT CONCAT(1234, IFNULL(NULL, ''),'hello') 문자열결과
FROM  DUAL;

MYSQL에서 NULL때메 결과가 제대로 리턴되는 것을 방지하고 싶으면 ISNULL이라는 함수를 사용해서 NULL일 경우 빈문자열로 치환하도록 직접 변환해주면 됩니다!

오늘은 간단하게 오라클과 MYSQL에서 문자열을 합치는 방법에 대해 알아봤어요

비슷한듯 DBMS마다 차이점이 있긴 있죠?ㅎㅎ 도움이 되셨다면 공감! 감사드립니다. 다음 포스팅에서 또 찾아뵐게요~~