반응형

/*******************************************************************************************************************
-- 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 

 

반응형

+ Recent posts