[SQL 입문] 문자열 함수

05 - 1 문자열 함수

문자열 관련 함수에는 문자열 연결, 형식 변환, 공백 제거, 치환 등이 있습니다.

 

문자열과 문자열 연결하기 : CONCAT 함수

문자열과 문자열은 연결 연산자 +를 사용해 쉽게 연결할 수 있습니다.

-- 연결 연산자 +로 문자열을 연결하는 방법
SELECT symbol + ' : ' + company_name FROM nasdaq_company

또 다른 방법은 CONCAT 함수를 사용하는 것입니다. CONCAT 함수의 사용 방법은 함수의 인자로 연결할 문자열을 쉼표로 구분해 나열합니다.

-- CONCAT 함수로 문자열을 연결하는 방법
SELECT CONCAT('I', 'Love ', 'SQL')

-- CONCAT 함수로 열 이름과 문자열 연결
SELECT CONCAT (symbol, ' : ', company_name)
FROM nasdaq_company

 

문자열과 숫자 또는 날짜 연결하기 : CAST, CONVERT  함수

문자열이 아닌 값을 문자열에 연결해야 할 때 변환 함수인 CAST와 CONVERT 함수를 사용합니다. 변환 함수는 숫자형이나 날짜형 등의 데이터를 문자열과 같은 자료형으로 변환할 수 있습니다. CAST 함수는 다음과 같은 형태로 1개의 인자만 넘겨 사용합니다.

-- CAST 함수 사용 방법
CAST(expression AS datatype(length))

-- 숫자형 데이터를 문자열로 연결 : CAST 함수로 자료형 변환
SELECT CAST(ipo_year AS nvarchar(50) + ' ' + CAST(open_price AS nvarchar(50))
FROM nasdaq_company
WHERE symbol = 'MSFT'

CONVERT 함수 사용 방법은 2개의 인자를 넘겨 사용합니다.

-- CONVERT 함수 사용 방법
CONVERT(data_type [(length)], expression [, style])

-- 숫자형 데이터를 문자열로 연결 : CONVERT 함수로 자료형 변환
SELECT
CONVERT(nvarchar(50), ipo_year) + ' ' + CONVERT(nvarchar(50), open_price)
FROM nasdaq_company
WHERE symbol = 'MSFT'

CAST나 CONVERT 함수를 사용할 때 문자열의 길이를 지정할 수 있습니다. 그리고 문자열을 변환할 때 지정한 값이 실제 변환할 문자열의 길이보다 작으면 문자열이 잘립니다.

-- 크기를 임의로 제한한 문자열로 변환
SELECT CONVERT (nvarchar(5), '2021-01-01 23:59:59.999')

-- 2021-

숫자형을 문자열로 변환하되 문자열 크기를 작게 지정하면 산술 오버플로 오류가 발생합니다.

-- 10자리 숫자를 5자르 문자열로 변환 : 산술 오버플로 오류 발생
SELECT CONVERT (nvarchar(5), 1234567890)

-- 메시지 8115, 수준 16, 상태 2, 줄 1
-- expression을(를) 데이터 형식 nvarchar(으)로 변환하는 중 산술 오버플로 오류가 발생했습니다.

NULL과 문자열 연결하기 : ISNULL, COALESCE 함수

만약 문자열과 연결하려는 값이 NULL이면 결과는 NULL로 반환됩니다. 그래서 테이블에 NULL이 있다면 문자열 또는 숫자로 자료형을 변환해야 합니다. NULL은 ISNULL 함수나 COALESCE 함수로 자료형을 변환합니다. ISNULL 함수는 인자로 (열 이름, 대체할 값)과 같이 전달하면 해당 열 이름의 NULL을 대체할 값으로 변환하고, COALESCE 함수는 (열 이름 1, 열 이름 2, ···)와 같이 여러 열 이름을 인자로 전달합니다.

-- ISNULL 함수의 형 변환 기본 형식
ISNULL (check_expression, replacement_value)

-- NULL과 문자열 연결 : ISNULL 함수로 대쳇값을 전달해 형 변환
SELECT symbol, sector, industry, open_price FROM nasdaq_company
WHERE symbol = 'DBA'

SELECT symbol, isnull(sector, ''), open_price FROM nasdaq_company
WHERE symbol = 'DBA'

SELECT symbol, isnull(sector, industry), industry, open_price FROM nasdaq_company
WHERE symbol = 'DBA'

※ 만약 마지막 인자도 NULL이라면 NULL을 반환합니다.

 

-- NULL과 문자열 연결 : COALESCE 함수로 대쳇값을 전달해 형 변환
SELECT symbol, coalesce(sector, industry, open_price), industry, open_price
FROM nasdaq_company
WHERE symbol = 'DBA'

 

문자열을 소문자나 대문자로 변경하기 : LOWER, UPPER 함수

LOWER 함수와 UPPER 함수는 각각 문자열을 소문자와 대문자로 변경합니다.

SELECT 'Do it! SQL', LOWER('Do it! SQL'), UPPER('Do it! SQL')

 

문자열 공백 제거하기 : LTRIM, RTRIM, TRIM 함수

사용가자 어떤 데이터를 입력할 때 의도치 않게 공백을 입력하거나, 반대로 의도해 공백을 입력하는 경우가 있습니다. 데이터를 관리하는 입장에서 공백은 문제를 일으킬 수 있으므로 관리 대상입니다. 이처럼 실수로 입력한 공백을 없애는 작업이 필요할 때 공백 제거 함수를 사용합니다. LTRIM 함수와 RTRIM 함수에서 L은 왼쪽, R은 오른쪽을 의미하며 각각 문자열의 왼쪽(앞), 오른쪽(뒤)의 공백을 제거합니다. 만약 양쪽 공백을 제거하려면 TRIM 함수를 사용합니다.

-- 문자열의 앞, 뒤, 양쪽 공백을 제거 : LTRIM, RTRIM, TRIM 함수
SELECT
'   Do it! SQL', LTRIM('   Do it! SQL'),
'Do it! SQL   ', RTRIM('Do it! SQL   '),
'   Do it! SQL   ', TRIM('   Do it! SQL   ')

TRIM 함수의 또 다른 기능은 단어 앞, 뒤에 있는 공백과 마침표를 제거하는 것입니다. 공백과 마침표 제거 기능은 LTRIM 함수, RTRIM 함수에는 없으므로 알아 두면 유용합니다.

-- 문자열의 앞, 뒤 공백과 마침표 제거 : TRIM 함수
SELECT TRIM('.,! ' FROM '   #   Do it! SQL   .')

-- #   Do it SQL

 

문자열 길이 반환하기 : LEN 함수

LEN 함수는 문자열의 문자 개수를 반환합니다. LEN 함수의 특징은 문자 개수를 셀 때 앞의 공백은 포함하지만, 뒤의 공백은 포함하지 않습니다.

-- 문자열의 문자 개수 반환 : LEN 함수
SELECT LEN('   Do it! SQL'), LEN('Do it! SQL   ')

-- 14, 9

-- 특정 길이의 문자열 검색
SELECT LEN(company_name), company_name FROM nasdaq_company

 

특정 문자까지 문자열 길이 반환하기 : CHARINDEX 함수

CHARINDEX 함수는 지정한 특정 문자까지의 길이를 반환합니다. 만약 지정한 문자가 탐색 대상 문자열에 없으면 0을 반환합니다.

-- 지정한 특정 문자까지의 문자열 길이 반환 : CHARINDEX 함수
SELECT 'Do it! SQL', CHARINDEX('!', 'Do it! SQL') CHARINDEX('#', 'Do it! SQL')

-- Do it! SQL, 6, 0

 

지정한 길이만큼 문자열 반환하기 : LEFT, RIGHT 함수

LEFT 함수와 RIGHT 함수는 문자열 왼쪽 또는 오른쪽에서 시작해 정의한 위치까지 문자열을 반환합니다.

SELECT 'Do it! SQL', LEFT('Do it! SQL', 2), RIGHT('Do it! SQL', 2)

-- Do it! SQL, Do, QL

 

 

지정한 범위의 문자열 반환하기 : SUBSTRING 함수

SUBSTRING 함수는 지정한 범위의 문자열을 반환합니다. 2번째 인자에는 시작 범위를, 3번째 인자에는 시작 위치로부터 반환할 문자 개수를 입력합니다.

-- 지정한 범위의 문자열 반환하는 방법 : SUBSTRING 함수
SUBSTRING(expression, start, length)

-- 4번째 문자부터 2개의 문자를 반환 : 공백 포함
SELECT 'Do it SQL', SUBSTRING('Do it! SQL', 4, 2)

-- Do it SQL, it

SUBSTRING 함수는 열 이름을 전달해 사용할 수도 있습니다.

-- 특정 열에서 지정한 범위의 문자열 반환 : SUBSTRING 함수
SELECT(company_name, 2, 3). company_name FROM nasdaq_company

SUBSTRING 함수는 CHARINDEX 함수를 조합해 사용하는 경우가 많습니다.

-- 특정 열에서 지정한 범위의 문자열 반환 : SUBSTRING, CHARINDEX 함수
SELECT SUBSTRING('email@email.com', 1, CHARINDEX('@', 'email@email.com') - 1)

-- email

 

특정 문자를 다른 문자로 변경하기 : REPLACE 함수

REPLACE 함수는 지정 문자를 다른 문자로 대체합니다. 첫 번째 인자는 열 또는 문자를 입력하고, 두번째 인자는 변경하려는 문자열을, 세번째 인자는 변경 문자열을 입력합니다.

-- 특정 문자를 다른 문자로 변경하는 방법 : REPLACE 함수
REPLACE(string_expression, string_pattern, string_replacement)

-- 문자 A를 C로 변경
SELECT
REPLACE(symbol, 'A', 'C'), symbol
FROM nasdaq_company
WHERE symbol LIKE '%A%'

 

문자 반복하기 : REPLICATE 함수

REPLICATE 함수는 지정한 문자를 반복할 때 사용합니다. 반복할 문자와 반복 횟수를 인자로 전달합니다.

-- 지정한 문자를 반복하는 방법 : REPLICATE 함수
REPLICATE(string_expression, integer_expression)

-- 문자 0을 10번 반복
SELECT REPLICATE('0', 10)

-- 0000000000

-- 문자 A를 C로 변경해 10번 반복
SELECT
REPLACE(symbol, 'A', REPLICATE('C', 10)), symbol
FROM nasdaq_company
WHERE symbol LIKE '%A%'

 

공백 문자 반복하기 : SPACE 함수

SPACE 함수는 공백 문자를 반복합니다.

-- 공백 문자를 반복 : SPACE 함수
SELECT symbol + SPACE(10) + company_name FROM nasdaq_company

 

문자열 역순으로 표시하기 : REVERSE 함수

REVERSE 함수는 문자열을 거꾸로 정렬하는 함수입니다.

-- 문자열 역순으로 표시 : REVERSE 함수
SELECT 'Do it! SQL', REVERSE('Do it! SQL')

-- Do it! SQL, LQS !ti oD

-- 문자열 역순으로 표시 : 여러 함수를 조합
WITH ip_list (ip)
AS (
SELECT '192.168.0.1' UNION ALL
SELECT '10.6.100.99' UNION ALL
SELECT '8.8.8.8' UNION ALL
SELECT '192.200.212.113'
)
SELECT ip, SUBSTRING(ip, 1, LEN(ip) - CHARINDEX('.', REVERSE(ip)))
FROM ip_list

-- 192.168.0.1, 192.168.0
-- 10.6.100.99, 10.6.100
-- 8.8.8.8, 8.8.8
-- 192.200.212.113, 192.200.212

 

지정한 범위의 문자열 삭제하고 새 문자열 끼워 넣기 : STUFF 함수

STUFF 함수는 지정한 범위의 문자를 삭제하고 새 문자열을 끼워 넣습니다.

-- 지정한 범위의 문자열 삭제하고 새 문자열 입력 : STUFF 함수
SELECT STUFF ('Do it! XX SQL Server', 8, 2, N'마이크로소프트')

-- Do it! 마이크로소프트 SQL Server

STUFF 함수를 사용해 데이터를 검색할 때는 검색하려는 열 길이보다 긴 문자열을 넣어도 뒷부분이 잘리지 않습니다. 그 이유는 데이터를 검색한 뒤 결과를 보여 줄 때 변환하므로, 실제 저장된 데이터 길이에는 영향을 미치지 않기 때문입니다. 하지만 검색한 결과를 다시 저장하려고 할 때는 변환한 문자열 길이가 저장할 수 있는 문자열 길이보다 길 경우 뒷부분이 잘리거나 오류가 발생할 수 있습니다.

 

숫자를 문자열로 변환하기 : STR 함수

STR 함수는 CAST 함수나 CONVERT 함수처럼 숫자를 문자열로 변환합니다. 다만 STR 함수만의 특징은 2번째 인자로 전달하는 변환 길잇값이 변경할 문자열 길이보다 짧은 경우 오른쪽 맞춤으로 정렬한다는 것과, 3번째 인자로 전달하는 소수 부분 길이에 맞게 반올림해 표현한다는 것입니다.

-- 숫자를 문자열로 변환 : STR 함수
SELECT STR(123.45, 6, 1);

-- 123.5

만약 변환 길잇값이 변경할 문자열 길이보다 짧으면 변환 길잇값만큼 * 문자를 반환합니다.

-- 숫자를 문자열로 변환 : * 문자 반환
SELECT STR(123.45, 2, 2);

-- **

 

실전 SQL

퀴즈 1. nasdaq_company 테이블에서 company_name 열의 데이터 중 마침표(.)를 포함하는 데이터를 검색해 .를 ****로 변경하세요.

SELECT
company_name,
REPLACE(company_name ,SUBSTRING(company_name, CHARINDEX('.', company_name) + 1, LEN(company_name)), '****')
FROM nasdaq_company
WHERE company_name LIKE '%.%'