DBeaver)컬럼 출력시 사칙연산 넣기 & 문자다루는 함수

컬럼에 있던 데이터를 정직하게 출력만 하는건 재미가 없습니다.

실은 컬럼에 있던 데이터를 덧셈 뺄셈 곱셈 나눗셈으로 조작해서 출력하는 것도 가능한데 

그래서 오늘은 컬럼에 있던 데이터를 내 입맛에 맞게 조작해서 출력하고 회계 조작으로 검찰에 고발되는 법을 알아봅시다.

 

 

 

 

 

컬럼에 사칙연산 가능 

 

저번시간에 쓰던 card 테이블 보면 '사용금액' 컬럼이 있었습니다. 

근데 그 금액엔 10% 부가세가 포함이 되어있기 때문에

10% 부가세를 제외한 사용금액만 출력하고 싶으면 어떻게하죠? 

엑셀이면 = 옆의컬럼 * 0.9 라고 코드를 짰을 텐데 

SQL도 비슷합니다. 

 

 

select 사용금액 * 0.9 FROM card

그냥 컬럼명에 0.9 곱해버리면 됩니다.

그럼 사용금액 컬럼이 출력되는데 0.9 곱해져서 나옵니다. 

 

 

 

 

 

select 사용금액 * 0.9 AS 부가세제외, 연체횟수 + 100 FROM card

당연히 동시에 여러 컬럼도 가능하고

AS 쓰고 싶으면 마음대로 씁시다.

 

 

 

 

덧셈은 +

뺄셈은 - 

곱셈은 * 

나눗셈은 /

쓰면 됩니다.

 

 

 

 

 

 

 

컬럼끼리의 연산도 가능 

 

고객의 "결제당 평균 사용금액"을 출력하고 싶으면 어떻게할까요? 

사용금액을 결제횟수로 나누면 됩니다. 

다행히 SQL 에선 컬럼끼리의 사칙연산도 가능합니다. 

 

select 사용금액 / 결제횟수 FROM card

이러면 행마다 사용금액을 결제횟수로 나눠서 출력해줍니다. 

컬럼끼리도 연산이 가능하다는거 잘 기억해둡시다. 

 

 

 

 

 

 

 

 

문자 덧셈도 가능 

 

사칙연산은 당연히 숫자가 들어있는 컬럼에만 적용가능합니다.

문자컬럼은 뭘할 수 있냐고요? 

문자끼리 양옆을 이어붙여서 더할 수는 있습니다. 

 

 

 

예를 들어서 현재 테이블에서 "James" + "vip" 이렇게 컬럼에 있는 문자 2개를 이어붙여서 출력하고 싶은겁니다. 

그럼 어떻게 하냐면 문자합치기용 CONCAT() 함수를 사용합니다. 

 

 

 

SELECT CONCAT(고객명, 고객등급) FROM card 

이러면 고객명 + 고객등급 컬럼의 문자들을 합쳐서 출력해줍니다.

 

 

 

▲ 숫자만 덧셈할 수 있는게 아니었습니다.

 

 

 

 

 

 

SELECT CONCAT(고객명, ' is ', 사용금액) FROM card 

- CONCAT() 안에 여러 문자들을 집어넣을 수 있습니다.

- 컬럼이름말고 직접 문자 입력해도 합쳐줍니다.

- 숫자 입력해도 문자처럼 합쳐줍니다.

 

 

 

 

 

 

▲ 그래서 원하는 문장을 마음대로 만들 수 있습니다. 

 

 

 

 

 

SELECT 고객명 || ' is ' || 고객등급 FROM card 

MySQL, MariaDB는 CONCAT() 쓰면 되는데

그 외의 Postgres, Oracle 등의 DBMS는 || 기호를 사용해서 문자를 보통 합칩니다. 

+ 기호 쓰는 곳도 있습니다. 

 

 

 

 

 

 

문자 공백제거가능

 

문자 데이터에 쓸데없는 좌우 공백같은게 들어있을 수도 있는데 

그걸 제거하고 싶으면 

 

SELECT TRIM(컬럼명) FROM 어쩌구 

TRIM() 안에 넣으면 됩니다.

그럼 안에 있는 문자는 좌우 공백이 제거됩니다. 

 

 

 

 

 

 

REPLACE()

 

SELECT REPLACE('서울에사는 서울맨', '서울', '경기') 

원하는 단어를 다른 단어로 전부 바꿀 수 있습니다. 

REPLACE(바꿀문자, 이걸찾아서, 이걸로바꾸셈) 채워주면 됩니다.

위 코드는 '경기에 사는 경기맨' 이 출력됩니다.

참고로 SELECT 다음에 문자나 숫자만 달랑 넣어도 잘 출력해줌 

 

 

 

 

 

 

SUBSTR()

 

SELECT SUBSTR('abcdef', 3, 2) 

원하는 문자만 뽑아낼 수 있습니다. 

SUBSTR(문자, 몇번째부터, 몇자) 를 채우면 되는데 

그래서 위 코드는 3번째 부터 2개의 글자를 가져옵니다. 

그래서 'cd'만 출력될듯요 

 

 

 

 

 

 

INSERT()

 

SELECT INSERT('test@naver.com', 1, 4, 'hello') 

문자의 일부를 다른 단어로 교체할 수 있습니다.

INSERT(바꿀문자, 몇번째부터, 몇자를, 이걸로바꾸셈) 이거 채우면 됩니다.

위 코드는 그래서 hello@naver.com 이 출력됩니다. 

 

 

Postgres는 OVERLAY() 를 씁니다.

OVERLAY('txxxxas' PLACING 'hom' FROM 2 FOR 4) → thomas 가 출력됩니다.

Oracle은 없어서 SUBSTR() 써서 직접 하면 됩니다. 

 

 

 

 

 

 

 

아무튼 결론은 DBMS에선 문자나 숫자데이터를 쉽게 변형할 수 있는 함수들이 많이 제공됩니다.  

합하면 60개 가까이 있을듯요 

 

 

▲ 이걸 쭉 공책에 정리하고 외우는 분들이 있는데 

1990년대면 그렇게 하라고 강요했겠으나 요즘은 구글이 있기 때문에 외우는건 인생낭비입니다. 

어짜피 3일 후에 다 까먹기 때문에 

"컬럼에 있던 데이터를 맘대로 조작해서 출력가능하구나" 이것만 이해하고 가면 됩니다.

그리고 필요할 때 구글에 "MySQL 문자 공백제거" 검색하는게 올바른 코딩방법입니다.

 

 

 

 

 

Q1. 특정 문자에 있는 모든 공백을 제거해서 출력하려면 어떻게 코드를 짜야할까요?

아무 컬럼에다가 공백이 들어있는 문자 몇개 입력해서 테스트해봅시다. 

위 사진처럼 단어 사이의 공백이 있으면 그것도 제거되어야합니다. 

 

모르면 검색해야지 답 보고 치팅하면 어떻게합니까

 

 

SELECT REPLACE(컬럼명, ' ', '') FROM 테이블명 

이러면 상품명 컬럼을 출력해주는데 ' ' 문자는 '' 으로 바꿔줍니다.

공백을 빈 문자로 바꿔버리면 그게 공백제거죠 뭐

 

[collapse]

 

 

 

 

 

Q2. 위 컬럼에서 휴대폰 뒷자리 4글자만 출력하려면 어떻게 코드를 짜야할까요?

위처럼 대충 컬럼하나 만들어서 폰번호 집어넣고 테스트해봅시다. 

아마 컬럼만들 때 varchar() 데이터타입 써야할듯요 

 

답인데요

 

 

SELECT RIGHT(번호, 4) from 테이블명 

RIGHT() 이런거 쓰면 맨 뒤의 X자리 문자를 잘라서 출력해줍니다.

그걸 어떻게 알았냐고요? 

누구나 구글에 검색하면 알 수 있습니다. 

 

 

 

SELECT SUBSTR(번호, 10, 4) from 테이블명 

번호가 항상 13자리면 substr 가져다써도 됩니다.