SQL 함수: 문자열 함수

문자열 연결, 형식 변환, 공백 제거, 치환 등이 있다.

 

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

연결 연산자 +로 문자열을 연결하는 방법

SELECT symbol + ' : ' + comapny_name FROM nasdaq_company

CONCAT 함수로 문자열을 연결하는 방법

SELECT CONCAT('I', 'Love', 'SQL')

열에 적용할 때도 마찬가지다. 

CONCAT 함수로 열 이름과 문자열 연결

SELECT CONCAT (symbol, ' : ', company_name) FROM nasdaq_company

 

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

숫자형 데이터를 +로 연결하면 에러가 발생한다

CAST, CONVERT 함수는 숫자형이나 날짜형 등의 데이터를 문자열과 같은 자료형으로 변환할 수 있다. 

CAST 함수 사용법

CAST (expression As datatype(length))

SELECT 

     CAST (ipo_year AS nvarchar(50))  + '  ' + CAST(open_price AS nvarchar(50))

FROM nasdaq_company WHERE symbol ='MSFT'

CONVERT 함수 사용법

CONVERT (data_type[(length)], expression [, style])

SELECT 

       CONVERT (nvarchar(50), ipo_year)  + '  ' + CONVERT(nvarchar(50), open_price)

FROM nasdaq_company WHERE symbol ='MSFT'

 

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

문자열과 연결하려는 값이 NULL이면, 결과는 NULL로 반환된다. 따라서, 테이블에 NULL이 있다면 문자열 또는 숫자로 자료형을 변환해야 한다. NULL은 ISNULL함수나  COALESCE 함수로 자료형을 변환한다. 

ISNULL 함수의 형 변환 기본 형식

ISNULL (check_expression, replacement_value)

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

결과

symbol  sector  industry  open_price
DBA      NULL   NULL     19.3400001525879

 

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

→ isnull (sector, ''): sector에 NULL이 있으면 공백으로 대체

결과

symbol    (열 이름 없음)  open_price
DBA                                  19.3400001525879

 

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

→ isnull (sector, industry): sector에 NULL이 있으면 industry 로 대체

결과

symbol   (열 이름 없음)  industry    open_price
DBA       NULL                 NULL       19.3400001525879

 

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

→ coalesce (sector, industry, open_price): 첫번째 인자(sector)로 전달한 값이 NULL일 때, 다음 인자(industry)가 NULL이면, 그 다음 인자(open_price )를 차례로 대입

결과

symbol    (열 이름 없음)                   industry           open_price
DBA       19.3400001525879          NULL             19.3400001525879

 

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

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

결과

(열 이름 없음) (열 이름 없음) (열 이름 없음)
Do it SQL         do it sql             DO IT SQL

 

문자열 공백 제거하기: LTRIM, RTRIM, 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의 또 다른 기능은 단어 앞, 뒤에 있는 공백과 마침표를 제거하는 것이다. 

공백 제거 기능은 LTRM, RTRIM에는 없다.

SELECT TRIM( '. , !  '    FROM   '         #         Do it ! SQL      .')  

결과

#         Do it! SQL

 

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

LEN 함수는 문자 개수를 셀 때 앞의 공백은 포함하지만 뒤 공백은 포함하지 않는다. 

SELECT LEN( '         Do it ! SQL'),   LEN( ' Do it ! SQL       ')

결과

(열 이름 없음) (열 이름 없음)
20                       12

SELECT LEN( ), company_name FROM nasdaq_company

결과

(열 이름 없음)     company_name
31                         CMC Crypto 200 Index by Solacti
28                         Dow Jones Industrial Average
8                           FTSE 100
7                          S&P 500
16                        NASDAQ Composite
10                        Nikkei 225

 

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

CHARINDEX 함수는 지정한 특정 문자까지의 길이를 반환한다. 

SELECT  'Do it!! SQL', CHARINDEX ( '!', 'Do it!! SQL')

→'!'까지 문자열 길이 반환

결과

(열 이름 없음) (열 이름 없음)
Do it!! SQL       6

※ 지정한 문자가 탐색 대상 문자열에 없으면 0을 반환한다.

 

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

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

1	2	3	4	5	6	7
D	o	i	t	S	Q	L

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)

SELECT  'Do it!! SQL', SUBSTRING( 'Do it!! SQL', 4, 2)

결과

(열 이름 없음) (열 이름 없음)
Do it!! SQL           it

 

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

SELECT  SUBSTRING(company_name, 2, 3), company_name  FROM nasdaq_company

결과

(열 이름 없음)  company_name
MC                   CMC Crypto 200 Index by Solacti
ow                    Dow Jones Industrial Average
TSE                 FTSE 100
&P                   S&P 500
ASD                NASDAQ Composite
ikk                   Nikkei 225

 

SUBSTRING 함수는 CHARINDEX 함수를 조합해 사용하는 경우가 많다. 다음은 CHARINDEX 함수로 @까지 위치를 계산한 다음 그 값을 SUBSTRING 함수에 사용해 @바로 앞까지의 문자열을 검색하는 쿼리이다.

SELECT  SUBSTRING('email@email.com', 1, CHARINDEX ('@', 'email@email.com') -1)

결과

(열 이름 없음)
email

 

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

1번째 인자는 열 또는 문자를 입력하고, 2번째 인자는 변경하려는 문자열을 , 3번째 인자는 변경 문자를 입력한다.

REPLACE 함수 사용법

REPLACE(string_expression, string_pattern, string_replacement)

SELECT  REPLACE'(symbol, 'A', 'C'), symbol

FROM nasdaq_company WHERE symbol LIKE '%A%'

결과 

(열 이름 없음)  symbol
C                        A
CC                    AA
CCC                 AAC
CCCG              AACG
CCCIU             AACIU

 

문자 반복하기:  REPLICATE 함수

반복할 문자와 반복 횟수를 인자로 전달한다.

REPLICATE 함수 사용법

REPLICATE(string_expression, integer_expression)

SELECT  REPLICATE('0', 10)

결과 

(열 이름 없음)
0000000000

SELECT  REPLACE (symbol, 'A', REPLICATE( 'C', 10)), symbol

 

FROM nasdaq_company WHERE symbol LIKE '%A%'

→ 문자 A를 C로 바꾸되 10번 반복하라.

결과 

(열 이름 없음)                                                  symbol
CCCCCCCCCC                                                A
CCCCCCCCCCCCCCCCCCCC                 AA
CCCCCCCCCCCCCCCCCCCCC              AAC
CCCCCCCCCCCCCCCCCCCCCG           AACG
CCCCCCCCCCCCCCCCCCCCCIU          AACIU

 

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

SELECT symbol + SPACE(10) + company_name FROM nasdaq_company

→ symbol과 company_name을 결합하되 사이에 공백을 10개 띄워라

결과 

(열 이름 없음)
^CMC200          CMC Crypto 200 Index by Solacti
^DJI          Dow Jones Industrial Average
^FTSE          FTSE 100
^GSPC          S&P 500
^IXIC          NASDAQ Composite

 

문자열 연숙으로 표시하기:  REVERSE 함수

REVERSE 함수는 문자열을 거꾸로 정렬하는 함수이다. 다양한 문자열 함수와 혼합하면 이메일에서 도메인의 자릿수 구하기나 다음 예제에서 다루는 IP  대역 구하기 등 다양하게 활용할 수 있다. 

SELECT  'Do it! SQL', REVERSE( 'Do it! SQL')

결과 

(열 이름 없음) (열 이름 없음)
Do it! SQL         LQS !ti oD

 

예) IP  address의 3번째 부분 정보까지 검색하라

WITH ip_list(ip)

AS (

SELECT '192.168.9.1' UNION ALL

SELECT '10.6. 100.99' UNION ALL

SELECT '8.8.8.8' UNION ALL

SELECT '192.100.212.113' 

)

SELECT ip, SUBSTRING(ip, 1, LEN(ip) - CHARINDEX('.', REVERSE(ip)))

FROM ip_list

해석:

 SELECT '192.168.9.1', CHARINDEX('.', '192.168.9.1')  → '192.' 반환, 결과 4

 SELECT '192.168.9.1', CHARINDEX('.', REVERSE('192.168.9.1')): → '1.' 반환, 결과 2

→ LEN(ip) - CHARINDEX('.', REVERSE(ip)): ip 전체 길이에서 마지막 '.'의 자릿수를 뺀 길이를 반환 

결과 

ip                             (열 이름 없음)
192.168.9.1             192.168.9
10.6. 100.99            10.6. 100
8.8.8.8                      8.8.8
192.100.212.113    192.100.212

 

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

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

SELECT STUFF ('Do it! XX SQL Server', 8, 2, N'마이크로소프트')

→ 8번째에 위치한 X부터 2글자를 삭제하고, '마이크로소프트' 글자를 끼워넣어라  

결과 

(열 이름 없음)
Do it! 마이크로소프트 SQL Server

 

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

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

SELECT STR(123.45, 6, 1)

→ 123.45는 길이 6으로, 소수 부분은 길이1로 변환하라

SELECT STR(123.45, 2, 2)

결과 

(열 이름 없음)
**

→ 변환길이가 변경할 문자열 길이보다 짧으면 변환 길잇값 만큼 *로 반환한다.변환 길잇값은 2이고 변경할 문자열의 길이가 5이므로 **를 반환한다. 

 

퀴즈1. nasdaq_compnay 테이블에서 company_name 열의 데이터 중 마침표(.)를 포함하는 데이터를 검색해 .를 ****로 변경하세요.
SELECT company_name, 
REPLACE(company_name, SUBSTRING(company_name,CHARINDEX('.', company_name) +1, 
LEN(company_name)), '****')  as company_name1

FROM nasdaq_company
WHERE company_name like '%.%'

→ 해석: WHERE(... '%.%)': company_name 열의 데이터 중 마침표(.)를 포함하는 조건으로 데이터를 검색해,

REPLACE(열, 변경전 문자('.'), 변경후 문자('****')) 문을 이용해 하는데, 

변경전 문자 부분을 SUBSTRING(열, start, length) 함수를 이용해 지정 문자('.')의 시작 위치부터 그 이후까지의 길이(LEN())를 반환받는다. 지정 문자('.')의 시작 위치는 CHARINDEX('.', 열) 함수를 이용한다.

 

결과 

company_name                                                               company_name1
Agilent Technologies Inc. Common Stock                    Agilent Technologies Inc.****
Armada Acquisition Corp. I Unit                                    Armada Acquisition Corp.****
Artius Acquisition Inc. Class A Common Stock           Artius Acquisition Inc.****
Artius Acquisition Inc. Unit                                              Artius Acquisition Inc.****
Aadi Bioscience Inc. Common Stock                           Aadi Bioscience Inc.****