개발자의 메모장

SUBSTRING

입력 받은 문자열의 부분 문자열을 리턴한다.

SQL Server에서 문자열 관련 시작 번호는 1부터 시작한다. (0 아님)

사용법

SUBSTRING(문자열, 시작자리번호, 자를 문자 길이)

사용 예시

SELECT
    SUBSTRING('www.naver.com', 1, 3) AS [sub_string1]
    , SUBSTRING('www.naver.com', 5, 5) AS [sub_string2]

LEFT, RIGHT

입력 받은 문자열의 왼쪽 또는 오른쪽부터 원하는 길이만큼 잘라낸다.

사용법

LEFT(문자열, 자를 문자 길이)
RIGHT(문자열, 자를 문자 길이)

사용 예시

SELECT
    LEFT('www.naver.com', 3)
    , RIGHT('www.naver.com', 3)

CHARINDEX

입력 받은 문자열에서 특정 문자의 위치를 반환한다.

문자열의 앞 또는 특정 위치부터 문자를 검색할 수 있다.

시작 위치 미지정시 문자열의 처음부터 탐색한다.

사용법

CHARINDEX(찾을 문자열, 문자열, 시작 위치)

사용 예시

SELECT
    -- 1번째부터 찾기
    CHARINDEX('straw', 'Apple, Strawberry, Banana') AS [result1]
    -- 10번째부터 찾기
    , CHARINDEX('straw', 'Apple, Strawberry, Banana, Strawberry', 10) AS [result2]
    -- 없는 문자열 찾은 경우
    , CHARINDEX('bbb', 'Apple, Strawberry, Banana, Strawberry', 10) AS [result3]
    -- 대소문자 구분하여 찾기
    , CHARINDEX('straw', 'Apple, Strawberry, Banana' COLLATE Latin1_General_CS_AS) AS [result4]

대소문자 구분 : COLLATE Latin1_General_CS_AS

대소문자 구분안함 : COLLATE Latin1_General_CI_AS

특정 문자 위치에서 자르기

CHARINDEX, SUBSTRING을 이용하여 편리하게 자를 수 있다.

DECLARE @TEMP_STR VARCHAR(20) = '2011,2012'

SELECT SUBSTRING(@TEMP_STR, CHARINDEX(',', @TEMP_STR) + 1, LEN(@TEMP_STR)) AS [result]

출처

https://gent.tistory.com/347

https://gent.tistory.com/436

https://coding-factory.tistory.com/99

데이터 추출 요청으로 VARCHAR 타입의 'hh:mm' 데이터 합계 처리가 필요한 업무가 들어왔다.

문자열 타입이므로 시, 분 데이터를 파싱하고, 분 단위로 변환하여 합계를 구한 후 다시 시, 분으로 변환하여 'hh:mm' 포맷으로 변환해야 한다.

기초 데이터

DROP TABLE IF EXISTS #TIME_DATA;
CREATE TABLE #TIME_DATA (
    RUN_TIME VARCHAR(5)
);

-- 예시 데이터 추가
INSERT INTO #TIME_DATA (RUN_TIME) VALUES ('10:30'), ('12:45'), ('08:15'), ('55:29');

시, 분 데이터 파싱

SELECT CAST(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) AS INT) AS T_MIN
	, CAST(RIGHT(RUN_TIME, LEN(RUN_TIME) - CHARINDEX(':', RUN_TIME)) AS INT) AS T_SEC
	, SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2) AS TOTAL_MIN
FROM #TIME_DATA;

시 : SUBSTRING 함수를 이용하여 구분 문자 ':' 기준 앞까지 부분 문자열을 뽑는다.

분 : RIGHT 함수를 이용하여 오른쪽부터 ':' 기준 오른쪽 문자열 글자의 갯수만큼 부분 문자열을 뽑는다.

분으로 환산 STRING은 * 60 연산에 의해 자동으로 정수로 변환되므로 별도 타입 캐스팅 불필요

합계

SELECT SUM(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2)) AS [총합계]
FROM #TIME_DATA;

SUM 함수를 이용하여 간단히 합계를 분단위로 구할 수 있다.

시, 분으로 변환

SELECT SUM(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2)) AS [총합분단위]
	, CAST(SUM(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2)) / 60 AS VARCHAR)
	+ ':'
	+ CAST(SUM(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2)) % 60 AS VARCHAR)
	AS [총합계 hh:mm]
FROM #TIME_DATA;

/, % 연산자를 이용하여 적절한 시, 분 단위로 변환하고, 다시 문자열 타입으로 변환 후 합치면 된다.

MS-SQL에 SPLIT 함수가 별도 존재하지 않으므로 직접 만들어 사용해야 한다.

입력된 문자열의 ':' 기준으로 문자열을 자른다면 아래 쿼리를 통해 구현할 수 있다.

DECLARE @IN_STR VARCHAR(100) = 'SECTION1.SECTION2.SECTION3:1234:2345:6789';

SELECT SUBSTRING(@IN_STR, 1, CHARINDEX(':', @IN_STR) - 1) AS [앞부분]
	, RIGHT(@IN_STR, LEN(@IN_STR) - CHARINDEX(':', @IN_STR)) AS [뒷부분];

SUBSTRING : 문자열의 시작 위치부터 일정 영역만큼 잘라내어 반환하다.

CHARINDEX : 문자열에서 특정 문자의 위치를 반환한다.

RIGHT, LEFT : 문자열을 받아서 오른쪽/왼쪽부터 원하는 길이만큼 잘라 반환한다.

단순하게 사용시 위 로직으로도 충분하지만, 스플릿할 문자가 여러 개인 경우에 로직이 복잡해질 수 있다.

이를 위해 자주 사용하는 함수를 만들어보았다.

CREATE FUNCTION [dbo].[UFN_SPLIT]
(  
	@IN_STR			VARCHAR(MAX),		/* 문자열 */
	@IDX			INT,				/* 추출순번 */
	@SEPARATOR		VARCHAR(10) = ','	/* SPLIT 기준문자 */
)  
RETURNS  VARCHAR(200)   
AS  
BEGIN  

DECLARE @TEMP_STR		VARCHAR(200)  
DECLARE @CUR_STR		VARCHAR(200)  
DECLARE @CUR_SEPARATOR	VARCHAR(10)  
DECLARE @I				INT  
    
SET @TEMP_STR = '';
SET @I = 1;
SET @CUR_SEPARATOR = LTRIM(RTRIM(@SEPARATOR));
SET @CUR_STR  = LTRIM(RTRIM(@IN_STR)) + @CUR_SEPARATOR;  -- 마지막 문자 파싱하기 위해 맨 뒤에 separator 추가

IF CHARINDEX(@CUR_SEPARATOR, @IN_STR) > 0  
    BEGIN  
    WHILE @IDX >= @I
        BEGIN
        IF CHARINDEX(@CUR_SEPARATOR, @CUR_STR) > 0  
            BEGIN  
            -- 문자열 스플릿
            SET @TEMP_STR = SUBSTRING(@CUR_STR, 1, CHARINDEX(@CUR_SEPARATOR, @CUR_STR) - 1);
							
            -- 반환될 문자는 버린후 좌우공백 제거     
            SET @CUR_STR = RIGHT(@CUR_STR, LEN(@CUR_STR) - (LEN(@TEMP_STR) + LEN(@SEPARATOR)));
            END
        ELSE
            BEGIN
            SET @TEMP_STR  = ''
            END
						 
        SET @I = @I + 1  
        END
    END
ELSE
    BEGIN  
        SET @TEMP_STR  = @IN_STR
    END

RETURN(@TEMP_STR)

END
GO

도전 과제

입력 문자열에 'section1 /section2/section3' 형식으로 사이에 공백이 들어간 경우, 제대로 동작하지 않는다.

로직 중 LEN 함수는 입력된 문자열의 맨 뒤에 입력된 공백을 카운트하지 않기 때문으로 보인다.

참고 사항

SQL Server 2016 부터 STRING_SPLIT 함수가 추가되어, 컬럼 문자열의 구분자를 행으로 분리할 수 있게 되었다.