/*******************************************************************************************************************
-- Title : [SQL2017] 여러 연결된 반복 문자를 하나의 문자로 변환
-- Reference : 웹검색
-- Key word : tab /t 탭 consecutive string function 함수
*******************************************************************************************************************/
■ Scripts
SET NOCOUNT ON;
-- declare variable
DECLARE @text NVARCHAR(max)
SET @text = N'가 나 다 라';
-- replace 'consecutive string' to 'one space'
DECLARE @alterchar_1st NVARCHAR(20) = '【¿\t*】【¡\t#】';
DECLARE @alterchar_2st NVARCHAR(20) = '【¡\t#】【¿\t*】';
SELECT replace(@text, CHAR(9), @alterchar_1st);
SELECT replace(replace(@text, CHAR(9), @alterchar_1st), @alterchar_2st, '');
SELECT replace(replace(replace(@text, CHAR(9), @alterchar_1st), @alterchar_2st, ''), @alterchar_1st ,' ');
■ Result
-----------------------------------------------------------------------------------------------------------------------------------------------
가【¿\t*】【¡\t#】나【¿\t*】【¡\t#】【¿\t*】【¡\t#】다【¿\t*】【¡\t#】【¿\t*】【¡\t#】【¿\t*】【¡\t#】라
-----------------------------------------------------------------------------------------------------------------------------------------------
가【¿\t*】【¡\t#】나【¿\t*】【¡\t#】다【¿\t*】【¡\t#】라
-----------------------------------------------------------------------------------------------------------------------------------------------
가 나 다 라
■ Function
USE tempdb;
GO
CREATE FUNCTION dbo.fn_consecutive_tab_2_char(@sentence NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @ret_val NVARCHAR(MAX);
-- replace 'consecut¡ve str¡ng' to 'one space'
DECLARE @alterchar_1st NVARCHAR(20) = '【¿\t*】【¡\t#】';
DECLARE @alterchar_2st NVARCHAR(20) = '【¡\t#】【¿\t*】';
SET @ret_val = replace(replace(replace(@sentence, CHAR(9), @alterchar_1st), @alterchar_2st, ''), @alterchar_1st ,' ');
RETURN(@ret_val);
-- select dbo.fn_consecutive_tab_2_char('가 나 다');
END;
GO