[MS-SQL] 문자열에서 숫자만 리턴하는 함수

-- =============================================
-- Author     : marsland@hanmail.net
-- Create date: 2011-11-11
-- Description: 문자열에서 숫자만 리턴한다.
-- =============================================
CREATE FUNCTION [dbo].[fn_ExtractNumber]
(
	@in_value		VARCHAR(MAX)
)
RETURNS @TAB_RETURN TABLE
(
	Item	VARCHAR(MAX)
)
AS
BEGIN
	DECLARE @cnt INT;
	SET @cnt = 1;
	
	DECLARE @buffer VARCHAR(MAX);
	SET @buffer = '';
	
	WHILE @cnt <= LEN(@in_value)
	BEGIN		
		DECLARE @tmp CHAR(1);
		SET @tmp = SUBSTRING(@in_value, @cnt, 1);	
	
		IF (ASCII(@tmp) >= 48) AND (ASCII(@tmp) <= 57)
		BEGIN			
			SET @buffer = @buffer + @tmp;
		END;
		
		SET @cnt = @cnt + 1;
	END;
	
	IF @buffer IS NOT NULL
		INSERT INTO @TAB_RETURN VALUES(@buffer);
	
	RETURN 
END