[MS-SQL] 문자열에서 숫자만 추출하기 (스칼라 반환 함수)

 

문자열에서 숫자만 추출하기 (스칼라 반환 함수)

 

---

포스팅 개요

문자열에 포함된 숫자만 추출하는 함수입니다. 실행 시 '스칼라 반환 함수'로 생성되어 필요할 때마다 쉽게 적용이 가능하며 주로 보이는 그대로 저장된 금액(1,999,999), 일자(2023년 1월 1일) 등의 형식이 표함 된 숫자를 계산하기 위해 사용합니다.

---

예제 쿼리

먼저 사용할 데이터베이스에 다음의 쿼리를 실행합니다.

-- 문자열에서 숫자만 뽑아내는 사용자 함수
CREATE FUNCTION [dbo].[fn_GetNumInString]
(
	@icur_num VARCHAR(50)
)
RETURNS INT
AS
BEGIN
	DECLARE @i INT
	DECLARE @temp_num VARCHAR(50)
	SET @temp_num = ''
	SET @icur_num = REPLACE(@icur_num, '.', '')
	SET @icur_num = REPLACE(@icur_num, '+', '')
	SET @icur_num = REPLACE(@icur_num, '-', '')
	SET @icur_num = REPLACE(@icur_num, '\', '')
	SET @icur_num = REPLACE(@icur_num, '$', '')
	SET @icur_num = REPLACE(@icur_num, ',', '')
	SET @i = 1
	WHILE @i <= LEN(@icur_num)
	BEGIN
	    IF ISNUMERIC(SUBSTRING(@icur_num, @i, 1)) = 1
	    BEGIN
	        SET @temp_num = @temp_num + SUBSTRING(@icur_num, @i, 1)
	    END
	    
	    SET @i = @i + 1
	END
	
	IF ISNUMERIC(@temp_num) = 0
	    SET @temp_num = 0
	    
	IF @temp_num = ''
	    SET @temp_num = 0
	    
	RETURN CAST(@temp_num AS INT)
END

정상적으로 생성하였으면 SSMS의 다음과 같은 위치에 생성된 함수 확인이 가능합니다.

함수 생성 확인

적용 시에는 원하는 문자열 또는 컬럼에 아래 블럭의 코드와 같이 적용하시면 됩니다.

-- 함수 적용시
SELECT dbo.fn_GetNumInString('문자열')

이제 간단한 예제를 통해 만들어진 함수를 사용해보겠습니다.

-- 입고 테이블을 중복 생성을 막기 위한 테이블 유무 검사
IF OBJECT_ID ('[dbo].[In_Table]') IS NOT NULL DROP TABLE [dbo].[In_Table]

-- 입고 테이블 생성
CREATE TABLE In_Table(
 [ID] [INT] IDENTITY (1, 1) NOT NULL, --Key = 증가
 [In_Date] [VARCHAR](10) NULL, -- 입고일자
 [Employee] [VARCHAR](50) NULL, -- 직원
 [Stock] [VARCHAR](50) NULL, -- 상품
 [Qty] [INT] NULL, -- 수량
 [Remark] [VARCHAR](50) NULL -- 적요
 PRIMARY KEY ([ID]))
 
-- 입고 테이블에 INSERT
INSERT INTO In_Table([In_Date],[Employee],[Stock],[Qty],[Remark]) VALUES 
('2022-12-01', '너울림', '배', 120, '대금 결제 : 250,000원'),
('2022-12-07', 'ullim', '사과', 230, ''),
('2022-12-08', 'ullim', '사과', 30, '대금 결제 : 320,000원'),
('2022-12-09', '너울림', '귤', 150, ''),
('2022-12-15', '너울림', '귤', 150, '대금 결제 : 170,000원'),
('2022-12-15', 'ullim', '바나나', 150, '대금 결제 : 400,000원')

-- 입고 테이블 조회
SELECT In_Date '입고일자', Stock '상품', Qty '수량', Remark '적요' FROM In_Table

-- 상품별 수량 및 대금 합계
SELECT Stock, SUM(Qty), SUM(dbo.fn_GetNumInString(Remark)) FROM In_Table GROUP BY Stock

---

실행 결과

상품 별로 수량 합계, 금액 합계를 계산해보았습니다. 하지만 예제를 위한 가상의 상황이고 실제 금액과 관련된 데이터는 예제와 같이 허술하게 다루지는 않습니다.

실행 결과

---

참고 링크

 

2022.12.24 - [DataBase/MS-SQL] - [MS-SQL] 다중 레코드 INSERT 하기

[MS-SQL] 다중 레코드 INSERT 하기